Is there a way to insert into two tables with FK from one to another?

Question

I will give an example of the pseudocode of my current method, and if anyone knows about a method that does not work one line at a time, I would be very grateful. I am 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 would rather do some kind of insertion into both tables at the same time, because the cursor and the loop are slow.

Before I talk about why I insert something into two separate tables that are 1 to 1 related, this is because the first table cannot be changed, and I need the information in the second (temporary) table to reference for data conversion later.

No, I can’t add a temporary column to store the referenced data in a table that cannot be modified because it cannot be changed. This is a living system, and I do not have permission to modify the table.

Thanks!

Additional Information:

Example

 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 

Answer 1

You can use merge in Table1 and output in 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 with respect to Table1 , you cannot directly output the result to Table2 . In this case, you can use the temporary table as the output target and insert into Table2 from the temporary table.

Answer 2

With so much data, the best option would be to highlight updates on the system, allow identity insert insertion, and formulate keys before insertion.

---

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

Answer 3

Use the IDENTITY_INSERT, VIEW trigger, and INSTEAD OF trigger, and pre-populated identification values ​​with your insert.

See this SQL script .

I tried to use various things in the INSTEAD OF trigger to allow the T1 identity column to be used for T2, but in the end I failed.

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 

In this example, I cannot do better ...