Okay setting the scene. I have three tables, (Table1
, Table2
and DataTable
) and I want to insert into Table1
and Table2
using DataTable
as source.
So for every row in DataTable
I want a row in Table1
and Table2
, and Table2
needs to have the inserted id
(PK) from Table1
…
If I were to do this…
INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable
I'd get the ID
of the last inserted record into Table1
.
Is a CURSOR
or WHILE
loop the only ways to do this?
Best Answer
A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves.
Anyway, the solution would look something like this:
MERGE, as opposed to the other DML statements, can reference other tables than just
inserted
anddeleted
, which is useful for you here.More: http://sqlsunday.com/2013/08/04/cool-merge-features/