배치 처리 삽입 문을 작성 중이며 임시 테이블을 사용하여 항목을 직접 루핑하고 삽입 된 각 행에 대해 SCOPE_IDENTITY ()를 호출하는 대신 삽입 된 ID를 추적하려고합니다.
삽입 해야하는 데이터에는 (임시) ID가 다른 테이블에 삽입 해야하는 다른 데이터와 연결되어 있으므로 실제 ID와 임시 ID에 대한 상호 참조가 필요합니다.
이것은 내가 지금까지 가지고있는 것의 예입니다.
-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));
-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');
DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);
INSERT INTO @MyTable ( [Name] )
OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef
SELECT [NAME] FROM @MyInsertData INS
-- Check the result
SELECT * FROM @MyCrossRef
문제는 ID를 수락하기 위해 OUTPUT INTO 절을 얻을 수 없다는 @MyInsertData.ID
것과 테이블을 결합하는 다른 트릭을 시도 했지만 아무것도 작동하지 않는 것입니다.
답변
사실, 당신은 당신을 변경하여 같은 일을 달성 할 수 INSERT
A를 MERGE
. 그동안 MERGE
문은 SQL Server의 “upserts”을 할 수있는 꽤 깔끔한 방법은 사실, 그냥 삽입하는 용도로 사용에서 당신을 중지 아무것도 없다 :
-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));
-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');
DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);
MERGE INTO @MyTable AS dest
USING @MyInsertData AS ins ON 1=0 -- always false
WHEN NOT MATCHED BY TARGET -- happens for every row, because 1 is never 0
THEN INSERT ([Name])
VALUES (ins.[NAME])
OUTPUT inserted.ID, ins.ID
INTO @MyCrossRef (NewId, OldId);
-- Check the result
SELECT * FROM @MyCrossRef
좋은 것들 중 하나에 대한 MERGE
당신이 액세스 할 수 있다는 것입니다 소스 열을 내장뿐만 아니라 inserted
및 deleted
의 테이블 OUTPUT
절.
실제로 테스트하지 않은 코드에 오류가있을 수 있습니다. 몇 년 전의 내 블로그 게시물 은 쿼리 성능을 포함하여 조금 더 자세히 설명합니다.
답변
output 절은 SELECT
트리거에서 작업하는 것처럼 소스의 다른 곳에서 데이터가 아닌 대상 행 및 상수 / 변수의 데이터에만 액세스 할 수 있습니다.
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql 상태 :
수정중인 테이블의 열에 대한 참조는 INSERTED 또는 DELETED 접두어로 규정해야합니다.
따라서 원래 ID를 얻으려면 출력 테이블이 다음과 같이 에코 할 수 있도록 대상 테이블에 포함시켜야합니다.
-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX), SourceID INT);
-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');
DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);
INSERT INTO @MyTable ( [Name], SourceID )
OUTPUT Inserted.ID, Inserted.SourceID INTO @MyCrossRef
SELECT [NAME], ID FROM @MyInsertData INS
-- Check the result
SELECT * FROM @MyCrossRef
대상 개체의 스키마를 변경하는 것은 상황에 따라 실용적이지 않을 수 있으므로 적용 할 수 없습니다.