如何在 SQL Server 中使用'scope_identity()”高效执行批量插入？

利用存储过程高效执行批量插入及scope_identity()

引言

本文提出一种基于存储过程的方法，以提高涉及scope_identity()的批量插入性能。它利用表值参数、MERGE语句和OUTPUT子句，高效地将记录插入到相关表中，并使用映射表捕获生成的ID。

示例表

我们首先创建示例表tblBase和tblRelated，它们之间存在一对多关系。

CREATE TABLE tblBase (
    base_id int identity(1,1) primary key, 
    base_data int
); 

CREATE TABLE tblRelated
(
    related_base_id int foreign key references tblBase (base_id), 
    related_Id int identity(1,1) primary key, 
    related_data int
);

用户自定义表类型

接下来，我们定义用户自定义表类型 (UDT) 来表示tblBase和tblRelated表的输入数据。

CREATE TYPE udt_base As Table
(
    base_temp_id int,  -- 注意：填充表值参数时，此列保存用于连接`tblBase`和`tblRelated`表的ID。
    base_id int,
    base_data int  
);

CREATE TYPE udt_related As Table
(
    related_base_id int,
    related_data int
);

CREATE TYPE udt_idMap as table
(
    temp_id int,
    id int
);

存储过程

以下stp_InsertMultipleRecordsToMultipleTables存储过程接受udt_base和udt_related UDT作为输入，并执行批量插入操作。

CREATE PROCEDURE stp_InsertMultipleRecordsToMultipleTables
(
    @base as dbo.udt_base readonly,
    @related as dbo.udt_related readonly
)
AS

DECLARE @idMap as dbo.udt_idMap

MERGE INTO tblBase USING @base AS temp ON 1 = 0 -- 始终不匹配
WHEN NOT MATCHED THEN
    INSERT (base_data)
    VALUES (temp.base_data)
    OUTPUT temp.base_temp_id, inserted.base_id -- 此处我们使用`base_temp_id`映射到正确的ID
    INTO @idMap (temp_id, id);

INSERT INTO tblRelated(related_base_id, related_data)
SELECT id, related_data
FROM @related r
INNER JOIN @idMap m ON(r.related_base_id = m.temp_id) -- 此处我们使用映射表插入具有正确`base_id`的相关记录

说明

• 该存储过程接受UDT作为输入。
• 它使用MERGE语句将记录插入tblBase。此方法确保表被更新或插入（如果存在匹配记录则更新）。
• OUTPUT子句捕获生成的base_id和用于连接相关表的临时base_temp_id。
• 这些值存储在@idMap表中。
• 然后，该过程使用@idMap将相关记录插入tblRelated，确保维护外键关系。

测试

在10条父记录和1000条子记录上进行测试，该过程在不到1秒的时间内执行完毕。

结论

这种基于存储过程的方法为使用scope_identity()的批量插入提供了显著的性能改进。它消除了在循环中查询的需要，减少了数据库调用次数和锁定时间。此外，它利用表值参数和表值函数 (UDF) 来高效地操作数据。

以上是如何在 SQL Server 中使用'scope_identity()”高效执行批量插入？的详细内容。更多信息请关注PHP中文网其他相关文章！