views:

32

answers:

1

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
A: 

With that much data, the best option might be to isolate updates on the system, allow identity insert and prepoulate the keys before inserting.


Or, can you just do the first insert, then modify the insert for the second (from a temp table) to join on to the original data and find the key.

ck
Great ideas and thank you, but... I can't modify the first table to allow identity insert.The second idea would be perfect but the reason I need the second table with a foreign key to the first is because the data I'm inserting into the first doesn't contain anything to make each record unique.The second destination table would essentially contain the source table's identity.See additional info I added to the question.
AppFzx