I'll give a pseudocode example of my current method and if anyone knows of a method that doesn't work one row at a time, I'd be quite appreciative. I'm using MS SQLServer 2008
define cursor for the data to be inserted (about 3 million records)
loop
(
insert record into table 1
use scope_identity() to get key
insert record into table 2 that references table 1
)
I'd much rather do some sort of insert into both tables simultaneously because a cursor and loop are slow.
Before anyone rants about why I'm inserting something into two separate tables that has a 1 to 1 relationship, it's because the first table cannot be modified and I need the information in the second (temporary) table for reference for data conversion operations later on. No, I cannot add a temporary column to hold the reference data on to the table that cannot be modified because it cannot be modified. This is a live system and I don't have permissions to alter the table.
Thanks!
Additional Info:
Ex
Source:
1 a big monkey
2 a tall elephant
3 a big giraffe
4 a tiny cow
5 a tall cow
Dest:
Table 1 Table 2
monkey 1 big
elephant 2 tall
giraffe 3 big
cow 4 tiny
cow 5 cow