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