SQL Server : add multiple rows in one sql query

Question

I want to insert multiple rows in one query like this:

insert into EducationInfo (student_id, school_id)
values (
    (select id
     from STUDENT
     where iin in (select distinct iinplt
                   from TEST_STUDENT
                   where id_university = 9)), 23421
       )

Of course it is impossible. I know that I can do it like this:

 values(1,23421),
 values(2,23421)...

but as you can see I don't know ids from student_id column and there are too many ids. Is there any possible way to do it?

Gordon Linoff · Accepted Answer · 2019-09-23 12:17:30Z

5

Use insert . . . select:

insert into nedb.EducationInfo (student_id, school_id)
     select id, 23421
     from nedb.student
     where iin in (select iinplt
                   from IMPORT_DATA.esuvo_students
                   where id_university = 9
                  );

Note that select distinct is unnecessary when using in.

answered Sep 23, 2019 at 12:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LongChalk_Rotem_Meron · Accepted Answer · 2019-09-23 12:52:56Z

1

Use insert ... select ... join, that way you can manipulate your join to be something far more elaborate that just "in"

insert into nedb.EducationInfo (student_id, school_id)
 select distinct s.id, 23421
 from nedb.student s
 join (SELECT distinct iinplt FROM IMPORT_DATA.esuvo_students 
       WHERE id_university = 9 ) es ON s.iin = es.iinplt

make sure you don't have any many to many JOIN matches on your ON statement, otherwise it might slow the query down

answered Sep 23, 2019 at 12:52

LongChalk_Rotem_Meron

81311 silver badges14 bronze badges

1 Comment

Mukhamedali Zhadigerov Over a year ago

Very nice solution! Thank you!

Collectives™ on Stack Overflow

SQL Server : add multiple rows in one sql query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related