主页 / dba / 问题 / 191146
Accepted
Louis Somers
Asked: 2017-11-18 03:57:22 +0800 CST

在 INSERT 语句的 OUTPUT INTO 子句中使用源列 (SQL Server)

  • 772

我正在编写批处理插入语句，并希望使用临时表来跟踪插入的 ID，而不是自己循环遍历项目并为每个插入的行调用 SCOPE_IDENTITY()。

需要插入的数据具有（临时）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

问题是我无法让 OUTPUT INTO 子句接受 ID，我已经尝试过@MyInsertData.ID将表连接到自身的其他技巧，但似乎没有任何效果。

insert output-clause

2 个回答

  1. Best Answer

    实际上，您可以通过将您的更改INSERT为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

    我的代码可能包含错误，因为我还没有实际测试过它。我几年前的博客文章更详细地介绍了一些细节，包括查询性能。

    • 40

  2. 输出子句只能访问目标行和常量/变量中的数据，不能访问源中其他地方的数据SELECT，就像您在触发器中操作一样。

    https://learn.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

    尽管在您的情况下更改目标对象的架构可能不切实际，因此这可能不适用。

    • 8

相关问题