How to Efficiently Perform Bulk Inserts with `scope_identity()` in SQL Server?

Use stored procedures to efficiently perform batch inserts and scope_identity()

Introduction

This article proposes a method based on stored procedures to improve the performance of batch inserts involving scope_identity(). It utilizes table-valued parameters, MERGE statements, and OUTPUT clauses to efficiently insert records into related tables and captures the generated IDs using a mapping table.

Example table

We first create example tables tblBase and tblRelated, which have a one-to-many relationship between them.

<code class="language-sql">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
);</code>

User-defined table type

Next, we define user-defined table types (UDTs) to represent the input data for the tblBase and tblRelated tables.

<code class="language-sql">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
);</code>

Stored Procedure

The following stp_InsertMultipleRecordsToMultipleTables stored procedure accepts udt_base and udt_related UDTs as input and performs bulk insert operations.

<code class="language-sql">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`的相关记录</code>

Description

• This stored procedure accepts UDT as input.
• It uses the MERGE statement to insert records into tblBase. This method ensures that the table is updated or inserted (updated if a matching record exists).
The
• OUTPUT clause captures the generated base_id and the temporary base_temp_id used to join the related tables.
• These values ​​are stored in the @idMap table.
• The process then uses @idMap to insert the related records into tblRelated, ensuring that foreign key relationships are maintained.

Test

Tested on 10 parent records and 1000 child records, the process executed in less than 1 second.

Conclusion

This stored procedure-based approach provides significant performance improvements for batch inserts using scope_identity(). It eliminates the need to query in a loop, reducing the number of database calls and locking time. Additionally, it leverages table-valued parameters and table-valued functions (UDFs) to efficiently manipulate data.

The above is the detailed content of How to Efficiently Perform Bulk Inserts with `scope_identity()` in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!