Is there a way to bulk insert into two tables with FK from one to the other?

Question

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

Stoltzfus

Does this answer help? – Liaotung

Answer 1

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.

Britton

Answer 2

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.

Psychodiagnosis

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. – Stoltzfus

You don't need to modify the table to allow identity insert -- you just need to bracket your insert statement with SET IDENTITY_INSERT ON <tablename> and SET IDENTITY_INSERT OFF. – Kolinsky

Answer 3

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.

Kolinsky

Answer 4

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...

Constrictive