使用表值参数插入数据时获取多个scope_identity

在使用表值参数（Table-Valued Parameters, TVPs）插入数据并希望获取多个SCOPE_IDENTITY()值时，可能会遇到一些挑战。SCOPE_IDENTITY()函数返回的是当前会话和当前范围内最后一个插入的标识列的值。当使用TVPs插入多行数据时，SCOPE_IDENTITY()只能返回最后一行插入的标识值，而不是所有行的标识值。

基础概念

表值参数（TVPs）：允许将表作为参数传递给存储过程或函数。
SCOPE_IDENTITY()：返回当前会话和当前范围内最后一个插入的标识列的值。

应用场景

批量插入数据：当需要一次性插入大量数据时，使用TVPs可以提高效率。
复杂业务逻辑：在存储过程中处理复杂的数据插入逻辑时，TVPs提供了便利。

遇到的问题及原因

问题：使用TVPs插入多行数据时，无法直接获取每一行的SCOPE_IDENTITY()值。

原因：SCOPE_IDENTITY()只能返回当前范围内最后一个插入的标识值，而不是所有行的标识值。

解决方案

为了获取每一行的标识值，可以使用以下方法：

使用OUTPUT子句：在插入语句中使用OUTPUT子句将插入的标识值捕获到一个临时表中。

DECLARE @InsertedIds TABLE (Id INT);

INSERT INTO YourTable (Column1, Column2)
OUTPUT INSERTED.Id INTO @InsertedIds
SELECT Column1, Column2 FROM @YourTVP;

SELECT * FROM @InsertedIds;

使用临时表：先将数据插入到一个临时表中，然后逐行插入到目标表并捕获标识值。

CREATE TABLE #TempTable (Id INT IDENTITY(1,1), Column1 VARCHAR(50), Column2 INT);

INSERT INTO #TempTable (Column1, Column2)
SELECT Column1, Column2 FROM @YourTVP;

DECLARE @InsertedIds TABLE (Id INT);

INSERT INTO YourTable (Column1, Column2)
OUTPUT INSERTED.Id INTO @InsertedIds
SELECT Column1, Column2 FROM #TempTable;

SELECT * FROM @InsertedIds;

DROP TABLE #TempTable;

示例代码

假设有一个表Employees和一个表值参数@EmployeeTVP，我们希望插入多行数据并获取每一行的标识值。

-- 创建表值参数类型
CREATE TYPE EmployeeType AS TABLE (
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- 创建示例表
CREATE TABLE Employees (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- 声明表值参数
DECLARE @EmployeeTVP EmployeeType;

-- 插入示例数据到表值参数
INSERT INTO @EmployeeTVP (FirstName, LastName)
VALUES ('John', 'Doe'), ('Jane', 'Smith'), ('Mike', 'Johnson');

-- 使用OUTPUT子句捕获标识值
DECLARE @InsertedIds TABLE (Id INT);

INSERT INTO Employees (FirstName, LastName)
OUTPUT INSERTED.Id INTO @InsertedIds
SELECT FirstName, LastName FROM @EmployeeTVP;

-- 查询捕获的标识值
SELECT * FROM @InsertedIds;

通过上述方法，可以有效地获取使用表值参数插入的多行数据的标识值。