INSERT_IDENTITY를 사용하여 여러 관련 테이블에 select에 삽입 Table2필요가 삽입되어 id에서 (PK)을 Table1… 내가

알았어 장면 설정 나는 세 개의 테이블을 가지고 ( Table1, Table2그리고 DataTable) 난에 삽입 할 Table1Table2사용 DataTable소스로. 그래서 모든 행에 DataTable나는의 행을 원 Table1하고 Table2, 그리고 Table2필요가 삽입되어 id에서 (PK)을 Table1

내가 이렇게하면 …

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

내가 불렀 ID에 마지막으로 삽입 된 기록을 Table1.

A는 CURSOR또는 WHILE루프는이 작업을 수행 할 수있는 유일한 방법?



답변

하는 솔루션 힘의 당신이 다시 삽입 할 수 있도록 그들을 다른 테이블에 당신을 위해 일을, 모든 삽입 된 행에서 옷을 사는 OUTPUT 절을 사용하고 있습니다. 그러나 메모리가 제공되는 경우 Table2의 외래 키 제약 조건이 제한됩니다.

어쨌든 해결책은 다음과 같습니다.

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

다른 DML 문과 달리 MERGE 는 insertedand 및 이외의 다른 테이블을 참조 할 수 있으며 deleted여기서 유용합니다.

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


답변

이것이 정기적으로 수행하려는 계획 인 경우 (즉, 일회성 데이터 변환 연습이 아닌 응용 프로그램 논리의 일부 임) INSTEAD OF INSERT데이터 분할 관리 및 트리거 와 함께 Table1 및 Table2에 대한 뷰를 사용할 수 있습니다. 키 / 관계))-다음과 같이하십시오.

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

트리거는 다음과 같이 간단 할 수 있습니다.

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

뷰를 가정하면 다음과 같습니다.

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

또는 다른 테이블의 행과 일치하지 않는 각 테이블에 행이있을 수있는 경우 :

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;

(물론 SELECT보기를 원하지 않을 때 어떤 행이 출력되는지 는 중요하지 않으며 트리거가 마법을 수행하기 위해 SELECT템플릿을 제공하기 만하면됩니다 INSERT)

이것은이 경우 기본 키에 UUID 유형을 사용하려고한다고 가정합니다. table1에서 자동으로 증가하는 정수 키를 사용하는 경우 조금 더해야 할 일이 있습니다. 다음과 같은 것이 작동 할 수 있습니다.

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

실제로 한 쌍의 INSERT명령문은 일회성으로 직접 작동 할 수 있습니다 ( 키에 대해 INT IDENTITY또는 UNIQUEIDENTIFIER DEFAULT NEWID()유형을 사용하는지 여부 ).

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;

뷰의 필요성을 없애고 완전히 트리거하지만, 이것이 작업 인 경우 코드에서 종종 수행 할 때마다 뷰 + 트리거는 매번 여러 명령문의 필요성을 추상화하는 것을 고려할 가치가 있습니다.

주의 사항 : 위의 모든 SQL은 생각에서 입력되었으며 테스트되지 않았으므로 필요에 따라 작동한다는 보장이 있기 전에 작업이 필요합니다.


답변

당신이 원하는 것 같습니다 :

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

또는 각 테이블에 항상 행이있는 경우 하나의 테이블을 사용하십시오 …이 테이블을 여러 테이블로 나눌 이유가 있습니까?


답변

귀하의 질문과 다른 답변에 대한 의견을 읽음으로써 문제 DataTable를 두 개의 새로운 테이블로 분할 하여 문제를 해결하려는 것처럼 보입니다 .

나는 ? DataTable와 같은 단일 고유 필드가 아직 없다고 가정 합니다 IDENTITY(1,1). 그렇지 않은 경우 Table1and 에 데이터를 삽입하는 데 사용할 수있는 것을 추가해야합니다 Table2.

예를 들어; 내가 테스트 데이터에 삽입 된 샘플 스키마 만든 DataTable수정, DataTable가지고 IDENTITY(1,1)열을, 다음 모두에 데이터를 삽입하는 것을 사용 Table1하고 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

이 일은 나를 위해 일했습니다. 나는 매우 늦은 답변을 알고 있지만 다른 사람들을 도울 수 있습니다. 내가 사용하는 IDENT_CURRENT이전의 삽입에서 행의 신원을 얻을 수 있지만, 나를 위해 항상 하나의 행입니다.