Sql – Insert row in table for each id in another table

sqltsql

I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.

I have a table with 17 million + rows all have a unique ID. We have recently created a new table that will be used in conjunction with the previous table where the foreign key of the new table is the unique id of the old table.

For ex.
Table 1 – id, field1, field2, field3…
table 2 – table1.id, field1 …

The problem is since we are migrating this into a live environment, we need to back fill table 2 with a row containing the id from table 1 for each row in table 1.
ex, table 1 – 1, test, null
table 2 now needs to have: 1, null, … and so on for each row that is in table1. The main issue is that the ids are not all sequential in table 1 so we will have to read from table 1 and then insert based of the id of found into table 2.

Is there any easier way to go about this?
Thanks in advance
Joe

Also to clarify, table 2 will be new data and the only thing that it will contain from table 1 is the id to keep the foreign key relationship

Also this is sql server 2000

Best Solution

If I understand correctly, you want one record in table2 for each record in table1. Also I believe that apart from the reference to table1, table2 should initially contain blank rows.

So assuming

table1 (ID, field1, field2, ...)
table2 (ID, table1_ID, fieldA, fieldB,...)
-- where table1_ID is a reference to ID of table1

After creating table2 you can simply run this insert statement

insert into table2(table1_ID) 
select ID from table1