question

Katie 1 avatar image
Katie 1 asked

insert with a subquery

how can i do it, avoiding writing too many inserts insert into table A (value1, value2, value3) values(1,-1,(select id from table b where b.column3 = 27 and column4 in (1,2,3,4,5,6))) thanks,
sql-server-2008-r2query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
SirSQL avatar image
SirSQL answered
It's a little unclear what you are are trying to do here. If it's a case of just trying to insert those selected id's from table into table then try insert into table (value1, value2, value4) select 1, -1, id from table where column3 = 27 and column4 in (1,2,3,4,5,6)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Katie 1 avatar image Katie 1 commented ·
I am not trying to do a straight insert. I want to insert one record for every value in the in statement in select subquery. if i had to insert traditionally it would be like insert into table A (value1, value2, value3) values(1,-1,(select id from table b where b.column3 = 27 and column4 =1)) insert into table A (value1, value2, value3) values(1,-1,(select id from table b where b.column3 = 27 and column4 = 2)) so on another 4 inserts.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Then @SirSQL's query should do the job for you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.