sqlsql-servert-sqldynamic-queries

Inserting a dynamic number of rows into SQL Server


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.


Solution

  • INSERT [table 1]
    SELECT 'a','a','a','a','a', fk 
    FROM [table 2]