Bagaimana untuk Melaksanakan Sisipan Pukal dengan Cekap dengan `scope_identity()` dalam SQL Server?

Gunakan prosedur tersimpan untuk melaksanakan sisipan kelompok dengan cekap dan scope_identity()

Pengenalan

Artikel ini mencadangkan kaedah berdasarkan prosedur tersimpan untuk meningkatkan prestasi sisipan kelompok yang melibatkan scope_identity(). Ia menggunakan parameter bernilai jadual, pernyataan MERGE dan klausa OUTPUT untuk memasukkan rekod dengan cekap ke dalam jadual berkaitan dan menangkap ID yang dijana menggunakan jadual pemetaan.

Contoh jadual

Kami mula-mula membuat contoh jadual tblBase dan tblRelated, yang mempunyai hubungan satu-dengan-banyak antara mereka.

<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>

Jenis jadual yang ditentukan pengguna

Seterusnya, kami mentakrifkan jenis jadual takrif pengguna (UDT) untuk mewakili data input bagi jadual tblBase dan 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>

Prosedur Tersimpan

Prosedur tersimpan stp_InsertMultipleRecordsToMultipleTables berikut menerima udt_base dan udt_related UDT sebagai input dan melaksanakan operasi sisipan pukal.

<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>

Penerangan

• Prosedur tersimpan ini menerima UDT sebagai input.
• Ia menggunakan pernyataan MERGE untuk memasukkan rekod ke dalam tblBase. Kaedah ini memastikan bahawa jadual dikemas kini atau dimasukkan (dikemas kini jika rekod sepadan wujud).
Fasal
• OUTPUT menangkap base_id yang dijana dan base_temp_id sementara yang digunakan untuk menyertai jadual yang berkaitan.
• Nilai ini disimpan dalam jadual @idMap.
• Proses kemudian menggunakan @idMap untuk memasukkan rekod yang berkaitan ke dalam tblRelated, memastikan perhubungan utama asing dikekalkan.

Ujian

Diuji pada 10 rekod ibu bapa dan 1000 rekod kanak-kanak, proses itu dilaksanakan dalam masa kurang daripada 1 saat.

Kesimpulan

Pendekatan berasaskan prosedur tersimpan ini memberikan peningkatan prestasi yang ketara untuk sisipan kelompok menggunakan scope_identity(). Ia menghapuskan keperluan untuk membuat pertanyaan dalam gelung, mengurangkan bilangan panggilan pangkalan data dan masa mengunci. Selain itu, ia memanfaatkan parameter bernilai jadual dan fungsi nilai jadual (UDF) untuk memanipulasi data dengan cekap.

Atas ialah kandungan terperinci Bagaimana untuk Melaksanakan Sisipan Pukal dengan Cekap dengan `scope_identity()` dalam SQL Server?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!