利用預存程序高效率執行批次插入及scope_identity()
引言
本文提出一種基於預存程序的方法，以提高涉及scope_identity()的批次插入效能。它利用表值參數、MERGE語句和OUTPUT子句，有效率地將記錄插入相關表中，並使用映射表擷取產生的ID。
範例表
我們先建立範例表tblBase和tblRelated，它們之間存在一對多關係。
<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>使用者自訂表格類型
接下來，我們定義使用者自訂表格類型 (UDT) 來表示tblBase和tblRelated表的輸入資料。
<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>預存程序
以下stp_InsertMultipleRecordsToMultipleTables預存程序接受udt_base和udt_related UDT作為輸入，並執行批次插入操作。
<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>說明
tblBase。此方法可確保表格已更新或插入（如果存在符合記錄則更新）。 base_id和用於連接相關表的臨時base_temp_id。 @idMap表中。 @idMap將相關記錄插入tblRelated，確保維護外鍵關係。 檢定
在10筆父記錄和1000筆子記錄上進行測試，該過程在不到1秒的時間內執行完畢。
結論
這種基於預存程序的方法為使用scope_identity()的批次插入提供了顯著的效能改進。它消除了在循環中查詢的需要，減少了資料庫呼叫次數和鎖定時間。此外，它利用表值參數和表值函數 (UDF) 來有效地操作資料。
以上是如何在 SQL Server 中使用「scope_identity()」高效率執行批次插入？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
