Wstaw do select do wielu powiązanych tabel za pomocą INSERT_IDENTITY

OK, ustawienie sceny. Mam trzy tabele, ( Table1, Table2a DataTable) i chcę wstawić Table1i Table2używając DataTablejako źródła. Więc dla każdego wiersza DataTablechcę wiersz w Table1i Table2, i Table2muszę mieć wstawiony id(PK) z Table1...

Gdybym to zrobił ...

INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable

Chciałbym uzyskać IDod ostatniego rekordu wstawionego do Table1.

Czy jedynym sposobem na to jest pętla CURSORlub WHILE?

Rozwiązaniem, które może Ci się przydać , jest użycie klauzuli OUTPUT, która wyrzuca wszystkie wstawione wiersze, dzięki czemu możesz ponownie wstawić je do innej tabeli. Ogranicza to jednak ograniczenia klucza obcego w Tabeli 2, jeśli pamięć służy.

W każdym razie rozwiązanie wyglądałoby mniej więcej tak:

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, w przeciwieństwie do innych instrukcji DML, może odwoływać się do innych tabel niż tylko insertedi deleted, co jest dla Ciebie przydatne.

Więcej: http://sqlsunday.com/2013/08/04/cool-merge-features/

Jeśli jest to coś, co planujesz robić regularnie (tj. Jest to część logiki aplikacji, a nie jednorazowe ćwiczenie transformacji danych), możesz użyć widoku do tabeli 1 i tabeli 2 z INSTEAD OF INSERTwyzwalaczem do zarządzania dzieleniem danych (i aranżacją klucze / relacje) - wtedy po prostu zrobiłbyś:

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

a wyzwalacz może być tak prosty, jak:

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

zakładając, że widok jest podobny do:

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

lub jeśli w każdej tabeli mogą znajdować się wiersze bez pasujących wierszy w drugiej:

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;

(oczywiście co rzędy są wysyłane, gdy SELECTz punktu widzenia nie ma znaczenia, jeśli nie zamierza SELECTz niego i to tylko istnieje, aby zapewnić szablon INSERTdo za spust, aby zrobić swoją magię)

Zakłada się, że w tym przypadku zamierzasz użyć typu UUID dla klucza podstawowego - jeśli używasz automatycznie zwiększającego klucza liczby całkowitej w tabeli 1, jest trochę więcej do zrobienia. Może działać coś takiego:

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

i w rzeczywistości para INSERTinstrukcji może działać bezpośrednio jako taka jednorazowa (niezależnie od tego, czy używasz klawisza INT IDENTITYlub UNIQUEIDENTIFIER DEFAULT NEWID()typu dla klucza):

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;

całkowicie eliminując potrzebę widoku i wyzwalacza, chociaż jeśli jest to operacja, którą często będziesz wykonywać w kodzie, warto zobaczyć + wyzwalacz, aby za każdym razem wyodrębnić potrzebę wielu instrukcji.

CAVEAT: cały powyższy SQL został napisany z przemyślenia i nie został przetestowany, będzie potrzebował pracy, zanim będzie jakakolwiek gwarancja, że ​​będzie działał tak, jak potrzebujesz.

Wygląda na to, że chcesz:

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

A może po prostu użyj jednej tabeli, jeśli zawsze będziesz mieć wiersz w każdej tabeli ... czy masz dobry powód, aby podzielić je na wiele tabel?

Po przeczytaniu pytania i komentarzy do innych odpowiedzi wydaje się, że próbujesz rozwiązać problem DataTable, dzieląc je na dwie nowe tabele.

Zakładam, że DataTablenie ma już pojedynczego pola unikatowego, takiego jak IDENTITY(1,1)? Jeśli nie, być może powinieneś dodać taki, którego możesz użyć do wstawiania danych do Table1i Table2.

Jako przykład; Został utworzony przykładowy schemat, włożonej do danych testowych DataTable, zmodyfikowany DataTablemieć IDENTITY(1,1)kolumny, które następnie wykorzystywane do wstawiania danych do obu Table1i 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;

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

To działało dla mnie, wiem, że jest to bardzo późna odpowiedź, ale może pomóc innym. Użyłem IDENT_CURRENTuzyskać tożsamość wiersza z poprzedniej wstawki, ale dla mnie zawsze jest to jeden wiersz.