Is there a way to insert a dynamic number of rows from within sql server (.sql script) given the value of a look up, and setting one column for each insert? I want to attach a row with the foreign key of every row in a different table.
For instance:
table 1:
1 j k l m n 2-(fk)
2 j k l m n 3-(fk)
3 k u y k l 2-(fk)
table 2:
2 hi you
3 hey ho
Now say I wanted to add 2 rows to table 1, with the fk values from table 2 of 2 and 3.
So the final table would look like:
table 1:
1 j k l m n 2-(fk)
2 j k l m n 3-(fk)
3 k u y k l 2-(fk)
4 a a a a a 3-(fk)
5 a a a a a 2-(fk)
With a programming language, this would be easy. Psuedo: For (rows in table 2) DO Insert into table 1 'a a a a a rows.id'
How could this be accomplished within sql server?
Edit:
For every row in table 2, get it's id, set it as the fk in a row for table 1, and insert it.
INSERT [table 1]
SELECT 'a','a','a','a','a', fk
FROM [table 2]