Insert into select into multiple related tables using INSERT_IDENTITY

Question

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?

Answer 1

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 INTO Table1 AS t1
USING MyTable ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (A, B, C) VALUES (t1.A, t1.B, t1.C)

--- .. and INSERT into Table2:
OUTPUT inserted.ID, MyTable.D, MyTable.E, MyTable.F
INTO Table2 (ID, D, E, F);

MERGE, as opposed to the other DML statements, can reference other tables than just inserted and deleted, which is useful for you here.

More: http://sqlsunday.com/2013/08/04/cool-merge-features/

Answer 2

If this is something you are planning to do regularly (i.e. it is part of the application logic and not a one-off data transformation exercise) then you could use a view onto Table1 and Table2 with an INSTEAD OF INSERT trigger to manage splitting the data (and arranging the keys/relationships) - then you would just do:

INSERT newView SELECT NEWID(), A, B, C, D, E, F FROM MyTable

and the trigger could be as simple as:

CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS 
    INSERT table1 SELECT ID, A, B, C FROM inserted
    INSERT table2 SELECT ID, D, E, F FROM inserted
GO

assuming the view is something like:

CREATE VIEW newView AS 
SELECT table1.ID, A, B, C, D, E, F 
FROM table1 
    JOIN table2 ON table1.ID = table2.ID;

or if there might be rows in each table without matching rows in the other:

CREATE VIEW newView AS 
SELECT ISNULL(table1.ID, table2.ID), A, B, C, D, E, F 
FROM table1 
    FULL OUTER JOIN table2 ON table1.ID = table2.ID;

(of course what rows are output when you SELECT from the view is unimportant if you don't intend to SELECT from it and it only exists to provide a template to INSERT into for the trigger to do its magic)

This is assuming that you are intending to use a UUID type for your primary key in this case - if you are using an automatically incrementing integer key on table1 there is a little more work to do. Something like the following might work:

CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS 
    INSERT table1 (A, B, C) 
    SELECT A, B, C 
    FROM inserted;
    INSERT table2 (ID, D, E, F) 
    SELECT ID, D, E, F 
    FROM table1 AS t 
        JOIN inserted AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C;
GO

and in fact that pair of INSERT statements might work directly as a one-off like so (whether you are using an INT IDENTITY or UNIQUEIDENTIFIER DEFAULT NEWID() type for the key):

INSERT table1 (A, B, C) 
SELECT A, B, C 
FROM MyTable;
INSERT table2 (ID, D, E, F) 
SELECT ID, D, E, F 
FROM table1 AS t 
    JOIN MyTable AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C;

negating the need for the view and trigger completely, though if this is an operation you will be performing often in your code the view+trigger would still be worth considering to abstract out the need for multiple statements each time.

CAVEAT: all the above SQL has been typed from thought and not tested, it will need work before there is any guarantee it will work as you need.

Answer 3

Seems like you want:

INSERT dbo.Table1(A,B,C) SELECT A,B,C 
  FROM dbo.DataTable WHERE <identify one row>;

INSERT dbo.Table2(ID,D,E,F) SELECT SCOPE_IDENTITY(),D,E,F
  FROM dbo.DataTable WHERE <identify that same row>;

Or maybe just use one table, if you're always going to have a row in each table... do you have a good reason for splitting these up into multiple tables?

Answer 4

From reading your question, and the comments on the other answers, it seems like you are attempting to fix a problem with DataTable by splitting it into two new tables.

I assume DataTable does not already have a single unique-field such as an IDENTITY(1,1)? If not, perhaps you should add one that you could use for inserting data into Table1 and Table2.

By way of an example; I've created a sample schema, inserted test data into DataTable, modified DataTable to have an IDENTITY(1,1) column, then used that to insert data into both Table1 and Table2:

USE tempdb;
GO

CREATE TABLE dbo.DataTable
(
    A INT
    , B INT
    , C INT
    , D INT
    , E INT
    , F INT
);

INSERT INTO dbo.DataTable (A, B, C, D, E, F)
VALUES (1, 2, 3, 11, 12, 13)
    , (4, 5, 6, 14, 15, 16)
    , (7, 8, 9, 17, 18, 19);

CREATE TABLE dbo.Table1
(
    Table1PK INT NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , A INT
    , B INT
    , C INT
);

CREATE TABLE dbo.Table2
(
    Table2PK INT NOT NULL CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Table1PK INT NOT NULL CONSTRAINT FK_Table2_Table1_PK FOREIGN KEY REFERENCES dbo.Table1(Table1PK)
    , D INT
    , E INT
    , F INT
);

ALTER TABLE dbo.DataTable ADD TempCol INT NOT NULL IDENTITY(1,1);

SET IDENTITY_INSERT dbo.Table1 ON;

INSERT INTO Table1 (Table1PK, A, B, C)
SELECT TempCol, A, B, C 
FROM DataTable;

SET IDENTITY_INSERT dbo.Table1 OFF;

INSERT INTO Table2 
SELECT Table1PK, D, E, F 
FROM dbo.DataTable DT
    INNER JOIN dbo.Table1 T ON DT.TempCol = T.Table1PK;

SELECT *
FROM dbo.Table1;

SELECT *
FROM dbo.Table2;

Answer 5

INSERT INTO VouchersOtherDetail (
                                 [VouNo],
                                 [Location],
                                 [VouType],
                                 [VouDate],
                                 [InputDate],
                                 [CrossRefGoodsVouNo],
                                 [Reversed],
                                 [ReversalReference],
                                 [UserID]
                                 ) 
SELECT   
                                [VouNo],
                                [Location],
                                [VouType],
                                [VouDate],
                                [InputDate],
                                [CrossRefGoodsVouNo],
                                [Reversed],
                                [ReversalReference],
                                [UserID]
FROM @InsertTableForVoucherDetail           

INSERT INTO VouchersDrCrDetail (
                                [VouID],
                                [AccountCode],
                                [CrossReferAccountCode],
                                [Description],
                                [VouDrAmount],
                                [VouCrAmount],
                                [RunningBalance]
                               )
SELECT  -- IDENT_CURRENT to get the identity of row from previous insert
                                 IDENT_CURRENT('VouchersOtherDetail'), 
                                 [AccountCode],
                                 [CrossReferAccountCode],
                                 [Description],
                                 [VouDrAmount],
                                 [VouCrAmount],
                                 [RunningBalance]
FROM @InsertTableForDrAndCR

This thing worked for me, I know its very late reply but may help others. I used IDENT_CURRENT get the identity of row from previous insert, but for me it is always one row.