Is there a way to bulk insert into two tables with FK from one to the other?
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 SQL Server 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.
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 tall
You can use merge on Table1
and and output into Table2
.
merge Table1
using SourceTable
on 0 = 1
when not matched then
insert (Animal) values (SourceTable.Animal)
output inserted.ID, SourceTable.Size into Table2(ID, Size);
SQL Fiddle
Note: If Table2
has a foreign key defined against Table1
you can't do the output directly to Table2
. In that case you can use a temporary table as the target of the output and insert into Table2
from the temporary table.
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.
Use IDENTITY_INSERT, a VIEW and an INSTEAD OF trigger, and prepopulated identity values with your insert.
See this SQL Fiddle.
I tried flailing about with various things in the INSTEAD OF trigger to let the T1's identity column be used for T2, but ultimately I failed.
insert into table1
select substring(src.data, 8 /* assuming fixed length as exampled */, len(src.data))
from source src
insert into table2
select t1.id, substring(src.data, 3 /* skip 'a ' */, 7)
from
table1 t1
inner join source src
on substring(src.data, 8, len(src.data)) = t1.data
For the given example, i cannot do better...
精彩评论