GABUNG (Transact-SQL)

Berlaku untuk:SQL ServerDatabase Azure SQLInstans Terkelola Azure SQLAzure Synapse Analytics (hanya kumpulan SQL khusus)Database SQL di Microsoft FabricGudang di Microsoft Fabric

Pernyataan MERGE menjalankan operasi sisipkan, perbarui, atau hapus pada tabel target dari hasil gabungan dengan tabel sumber. Misalnya, sinkronkan dua tabel dengan menyisipkan, memperbarui, atau menghapus baris dalam satu tabel berdasarkan perbedaan yang ditemukan di tabel lain.

Artikel ini menyediakan sintaks, argumen, keterangan, izin, dan contoh yang berbeda berdasarkan versi produk yang dipilih. Pilih versi produk yang Anda inginkan dari daftar dropdown versi.

Konvensi sintaks transact-SQL

Syntax

Sintaks untuk SQL Server dan Azure SQL Database:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

Arguments

DENGAN <common_table_expression>

Menentukan kumpulan atau tampilan hasil bernama sementara, juga dikenal sebagai ekspresi tabel umum, yang ditentukan dalam cakupan MERGE pernyataan. Kumpulan hasil berasal dari kueri sederhana dan dirujuk oleh MERGE pernyataan. Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL).

TOP ( ekspresi ) [ PERCENT ]

Menentukan jumlah atau persentase baris yang terpengaruh. ekspresi dapat berupa angka atau persentase baris. Baris yang direferensikan dalam TOP ekspresi tidak diatur dalam urutan apa pun. Untuk informasi selengkapnya, lihat TOP (Transact-SQL).

Klausa TOP berlaku setelah seluruh tabel sumber dan seluruh tabel target bergabung dan baris gabungan yang tidak memenuhi syarat untuk tindakan sisipkan, perbarui, atau hapus dihapus. Klausul TOP selanjutnya mengurangi jumlah baris yang digabungkan ke nilai yang ditentukan. Tindakan ini (sisipkan, perbarui, atau hapus) berlaku untuk baris gabungan yang tersisa dengan cara yang tidak diurutkan. Artinya, tidak ada urutan di mana baris didistribusikan di antara tindakan yang ditentukan dalam WHEN klausul. Misalnya, menentukan TOP (10) memengaruhi 10 baris. Dari baris ini, 7 mungkin diperbarui dan 3 disisipkan, atau 1 mungkin dihapus, 5 diperbarui, dan 4 disisipkan, dan sebagainya.

Tanpa filter pada tabel sumber, MERGE pernyataan mungkin melakukan pemindaian tabel atau pemindaian indeks berkluster pada tabel sumber, serta pemindaian tabel atau pemindaian indeks kluster tabel target. Oleh karena itu, performa I/O terkadang terpengaruh bahkan ketika menggunakan TOP klausul untuk memodifikasi tabel besar dengan membuat beberapa batch. Dalam skenario ini, penting untuk memastikan bahwa semua batch berturut-turut menargetkan baris baru.

database_name

Nama database tempat target_table berada.

schema_name

Nama skema tempat target_table berada.

target_table

Tabel atau tampilan tempat baris <table_source> data dicocokkan berdasarkan <clause_search_condition>. target_table adalah target setiap operasi sisipkan, perbarui, atau hapus yang ditentukan oleh WHEN klausul MERGE pernyataan.

Jika target_table adalah tampilan, tindakan apa pun terhadapnya harus memenuhi kondisi untuk memperbarui tampilan. Untuk informasi selengkapnya, lihat Mengubah Data Melalui Tampilan.

target_table tidak bisa menjadi tabel jarak jauh. target_table tidak dapat memiliki aturan apa pun yang ditentukan di dalamnya. target_table tidak dapat menjadi tabel yang dioptimalkan memori.

Petunjuk dapat ditentukan sebagai <merge_hint>.

[SEBAGAI] table_alias

Nama alternatif untuk mereferensikan tabel untuk target_table.

MENGGUNAKAN <table_source>

Menentukan sumber data yang cocok dengan baris data di target_table berdasarkan <merge_search_condition>. Hasil dari kecocokan ini menentukan tindakan yang harus diambil oleh WHEN klausul MERGE pernyataan. <table_source> dapat berupa tabel jarak jauh atau tabel turunan yang mengakses tabel jarak jauh.

<table_source>bisa menjadi tabel turunan yang menggunakan konstruktor nilai tabel Transact-SQL untuk membuat tabel dengan menentukan beberapa baris.

[SEBAGAI] table_alias

Nama alternatif untuk mereferensikan tabel untuk table_source.

Untuk informasi selengkapnya tentang sintaksis dan argumen klausa ini, lihat FROM (Transact-SQL).

PADA <merge_search_condition>

Menentukan kondisi di mana <table_source> bergabung dengan target_table untuk menentukan di mana mereka cocok.

Caution

Penting untuk menentukan hanya kolom dari tabel target yang akan digunakan untuk tujuan pencocokan. Artinya, tentukan kolom dari tabel target yang dibandingkan dengan kolom tabel sumber yang sesuai. Jangan mencoba meningkatkan performa kueri dengan memfilter baris dalam tabel target dalam ON klausul; misalnya, seperti menentukan AND NOT target_table.column_x = value. Melakukannya dapat mengembalikan hasil yang tidak terduga dan salah.

KETIKA DICOCOKKAN MAKA <merge_matched>

Menentukan bahwa semua baris *target_table, yang cocok dengan baris yang dikembalikan oleh <table_source> ON <merge_search_condition>, dan memenuhi kondisi pencarian tambahan apa pun, diperbarui atau dihapus sesuai dengan <merge_matched> klausul.

Pernyataan dapat MERGE memiliki, paling banyak, dua WHEN MATCHED klausul. Jika dua klausa ditentukan, klausul pertama harus disertai dengan AND<search_condition> klausul. Untuk baris tertentu, klausul kedua WHEN MATCHED hanya diterapkan jika yang pertama tidak. Jika ada dua WHEN MATCHED klausa, seseorang harus menentukan UPDATE tindakan dan satu harus menentukan DELETE tindakan. Ketika UPDATE ditentukan dalam <merge_matched> klausa, dan lebih dari satu baris <table_source> cocok dengan baris di target_table berdasarkan <merge_search_condition>, SQL Server mengembalikan kesalahan. Pernyataan MERGE tidak dapat memperbarui baris yang sama lebih dari sekali, atau memperbarui dan menghapus baris yang sama.

KETIKA TIDAK COCOK [ BERDASARKAN TARGET ] MAKA <merge_not_matched>

Menentukan bahwa baris disisipkan ke dalam target_table untuk setiap baris yang dikembalikan oleh baris yang tidak cocok dengan <table_source> ON <merge_search_condition> baris di target_table, tetapi memenuhi kondisi pencarian tambahan, jika ada. Nilai yang akan disisipkan ditentukan oleh <merge_not_matched> klausa. Pernyataan hanya MERGE dapat memiliki satu WHEN NOT MATCHED [ BY TARGET ] klausa.

KETIKA TIDAK COCOK DENGAN SUMBER MAKA <merge_matched>

Menentukan bahwa semua baris *target_table, yang tidak cocok dengan baris yang dikembalikan oleh <table_source> ON <merge_search_condition>, dan yang memenuhi kondisi pencarian tambahan, diperbarui atau dihapus sesuai dengan <merge_matched> klausul.

Pernyataan tersebut MERGE dapat memiliki paling banyak dua WHEN NOT MATCHED BY SOURCE klausul. Jika dua klausa ditentukan, maka klausul pertama harus disertai dengan AND<clause_search_condition> klausa. Untuk baris tertentu, klausul kedua WHEN NOT MATCHED BY SOURCE hanya diterapkan jika yang pertama tidak. Jika ada dua WHEN NOT MATCHED BY SOURCE klausa, maka seseorang harus menentukan UPDATE tindakan dan satu harus menentukan DELETE tindakan. Hanya kolom dari tabel target yang dapat dirujuk di <clause_search_condition>.

Ketika tidak ada baris yang dikembalikan oleh <table_source>, kolom dalam tabel sumber tidak dapat diakses. Jika tindakan perbarui atau hapus yang ditentukan dalam <merge_matched> kolom referensi klausa dalam tabel sumber, kesalahan 207 (Nama kolom tidak valid) dikembalikan. Misalnya, klausa WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 dapat menyebabkan pernyataan gagal karena Col1 dalam tabel sumber tidak dapat diakses.

DAN <clause_search_condition>

Menentukan kondisi pencarian yang valid. Untuk informasi selengkapnya, lihat Kondisi pencarian (Transact-SQL).

<table_hint_limited>

Menentukan satu atau beberapa petunjuk tabel untuk diterapkan pada tabel target untuk setiap tindakan sisipkan, perbarui, atau hapus yang dilakukan oleh MERGE pernyataan. Kata WITH kunci dan tanda kurung diperlukan.

NOLOCK dan READUNCOMMITTED tidak diizinkan. Untuk informasi selengkapnya tentang petunjuk tabel, lihat Petunjuk tabel (Transact-SQL).

Menentukan TABLOCK petunjuk pada tabel yang merupakan target pernyataan memiliki efek yang INSERT sama dengan menentukan TABLOCKX petunjuk. Kunci eksklusif diambil di atas meja. Ketika FORCESEEK ditentukan, itu berlaku untuk instans implisit tabel target yang digabungkan dengan tabel sumber.

Caution

Menentukan READPAST dengan WHEN NOT MATCHED [ BY TARGET ] THEN INSERT dapat mengakibatkan INSERT operasi yang melanggar UNIQUE batasan.

INDEX ( index_val [ ,... n ] )

Menentukan nama atau ID dari satu atau beberapa indeks pada tabel target untuk melakukan gabungan implisit dengan tabel sumber. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

<output_clause>

Mengembalikan baris untuk setiap baris dalam target_table yang diperbarui, disisipkan, atau dihapus, tanpa urutan tertentu. $action dapat ditentukan dalam klausa output. $action adalah kolom jenis nvarchar(10) yang mengembalikan salah satu dari tiga nilai untuk setiap baris: INSERT, , UPDATEatau DELETE, sesuai dengan tindakan yang dilakukan pada baris tersebut. Klausa OUTPUT adalah cara yang disarankan untuk mengkueri atau menghitung baris yang dipengaruhi oleh MERGE. Untuk informasi selengkapnya tentang argumen dan perilaku klausa ini, lihat klausa OUTPUT (Transact-SQL).

OPTION ( <query_hint> [ ,... n ] )

Menentukan bahwa petunjuk pengoptimal digunakan untuk menyesuaikan cara Mesin Database memproses pernyataan. Untuk informasi selengkapnya, lihat Petunjuk kueri (Transact-SQL).

<merge_matched>

Menentukan tindakan perbarui atau hapus yang diterapkan ke semua baris target_table yang tidak cocok dengan baris yang dikembalikan oleh <table_source> ON <merge_search_condition>, dan yang memenuhi kondisi pencarian tambahan apa pun.

SET_CLAUSE SET <PEMBARUAN>

Menentukan daftar nama kolom atau variabel untuk diperbarui dalam tabel target dan nilai untuk memperbaruinya.

Untuk informasi selengkapnya tentang argumen klausul ini, lihat UPDATE (Transact-SQL). Mengatur variabel ke nilai yang sama dengan kolom tidak didukung.

DELETE

Menentukan bahwa baris yang cocok dengan baris di target_table dihapus.

<merge_not_matched>

Menentukan nilai yang akan disisipkan ke dalam tabel target.

( column_list )

Daftar satu atau beberapa kolom tabel target untuk menyisipkan data. Kolom harus ditentukan sebagai nama bagian tunggal, atau pernyataan gagal MERGE . column_list harus diapit tanda kurung dan dibatasi oleh koma.

NILAI ( values_list )

Daftar konstanta, variabel, atau ekspresi yang dipisahkan koma yang mengembalikan nilai untuk dimasukkan ke dalam tabel target. Ekspresi tidak boleh berisi EXECUTE pernyataan.

NILAI DEFAULT

Memaksa baris yang disisipkan untuk berisi nilai default yang ditentukan untuk setiap kolom.

Untuk informasi selengkapnya tentang klausa ini, lihat INSERT (Transact-SQL).

<search_condition>

Menentukan kondisi pencarian untuk menentukan <merge_search_condition> atau <clause_search_condition>. Untuk informasi selengkapnya tentang argumen untuk klausa ini, lihat Kondisi pencarian (Transact-SQL).

<pola pencarian grafik>

Menentukan pola pencocokan grafik. Untuk informasi selengkapnya tentang argumen untuk klausul ini, lihat MATCH (Transact-SQL).

Remarks

Perilaku bersyarat yang MERGE dijelaskan untuk pernyataan berfungsi paling baik ketika kedua tabel memiliki campuran karakteristik pencocokan yang kompleks. Misalnya, menyisipkan baris jika tidak ada, atau memperbarui baris jika cocok. Saat hanya memperbarui satu tabel berdasarkan baris tabel lain, tingkatkan performa dan skalabilitas dengan INSERTpernyataan , , UPDATE, dan DELETE . Contohnya:

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

Setidaknya salah satu dari tiga MATCHED klausul harus ditentukan, tetapi dapat ditentukan dalam urutan apa pun. Variabel tidak dapat diperbarui lebih dari sekali dalam klausa yang sama MATCHED .

Setiap tindakan sisipkan, perbarui, atau hapus yang ditentukan pada tabel target oleh MERGE pernyataan dibatasi oleh batasan apa pun yang ditentukan di dalamnya, termasuk batasan integritas referensial berjendela. Jika IGNORE_DUP_KEY untuk ON indeks unik apa pun pada tabel target, MERGE abaikan pengaturan ini.

Pernyataan ini MERGE memerlukan titik koma (;) sebagai terminator pernyataan. Kesalahan 10713 muncul ketika MERGE pernyataan dijalankan tanpa terminator.

Saat digunakan setelah MERGE, @@ROWCOUNT (Transact-SQL) mengembalikan jumlah total baris yang disisipkan, diperbarui, dan dihapus ke klien.

MERGE adalah kata kunci yang dicadangkan sepenuhnya ketika tingkat kompatibilitas database diatur ke 100 atau lebih tinggi. Pernyataan MERGE ini tersedia di bawah 90 tingkat kompatibilitas database dan 100 ; namun, kata kunci tidak sepenuhnya dicadangkan ketika tingkat kompatibilitas database diatur ke 90.

Caution

Jangan gunakan MERGE pernyataan saat menggunakan replikasi pembaruan antrean. Pemicu MERGE pembaruan antrean dan tidak kompatibel. MERGE Ganti pernyataan dengan INSERT pernyataan dan UPDATE .

Implementasi pemicu

Untuk setiap tindakan sisipkan, perbarui, atau hapus yang ditentukan dalam MERGE pernyataan, SQL Server mengaktifkan pemicu yang sesuai AFTER yang ditentukan pada tabel target, tetapi tidak menjamin tindakan mana yang akan diaktifkan pemicu terlebih dahulu atau terakhir. Pemicu yang ditentukan untuk tindakan yang sama mematuhi urutan yang Anda tentukan. Untuk informasi selengkapnya tentang pengaturan pemicu urutan penembakan, lihat Menentukan Pemicu Pertama dan Terakhir.

Jika tabel target memiliki pemicu OF yang diaktifkan INSTEAD yang ditentukan di dalamnya untuk tindakan sisipkan, perbarui, atau hapus yang dilakukan oleh MERGE pernyataan, itu harus memiliki pemicu OF yang diaktifkan INSTEAD untuk semua tindakan yang ditentukan dalam MERGE pernyataan.

Jika salah satu INSTEAD pemicu OF UPDATE atau INSTEAD OF DELETE ditentukan pada target_table, operasi pembaruan atau penghapusan tidak dijalankan. Sebagai gantinya, pemicu menembak dan tabel yang dimasukkan dan dihapus kemudian mengisi yang sesuai.

Jika ada INSTEAD pemicu OF INSERT yang ditentukan pada target_table, operasi penyisipan tidak dilakukan. Sebagai gantinya, tabel akan diisi dengan sesuai.

Note

Tidak seperti pernyataan , , dan terpisahINSERT, jumlah baris yang tercermin oleh UPDATE di dalam pemicu mungkin lebih DELETE tinggi. @@ROWCOUNT Pemicu @@ROWCOUNT di dalam apa pun AFTER (terlepas dari pernyataan modifikasi data yang diambil pemicu) akan mencerminkan jumlah total baris yang terpengaruh oleh MERGE. Misalnya, jika MERGE pernyataan menyisipkan satu baris, memperbarui satu baris, dan menghapus satu baris, @@ROWCOUNT akan menjadi tiga untuk pemicu apa pun AFTER , bahkan jika pemicu hanya dideklarasikan untuk INSERT pernyataan.

Permissions

SELECT Memerlukan izin pada tabel sumber dan INSERTizin , UPDATE, atau DELETE pada tabel target. Untuk informasi selengkapnya, lihat bagian Izin di artikel SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL), dan DELETE (Transact-SQL).

Praktik terbaik indeks

Dengan menggunakan pernyataan , MERGE Anda dapat mengganti pernyataan DML individual dengan satu pernyataan. Ini dapat meningkatkan performa kueri karena operasi dilakukan dalam satu pernyataan, oleh karena itu, meminimalkan berapa kali data dalam tabel sumber dan target diproses. Namun, perolehan performa bergantung pada memiliki indeks, gabungan, dan pertimbangan lain yang benar.

Untuk meningkatkan performa MERGE pernyataan, kami merekomendasikan panduan indeks berikut:

• Buat indeks untuk memfasilitasi gabungan antara sumber dan target MERGE:
  • Buat indeks pada kolom gabungan dalam tabel sumber yang memiliki kunci yang mencakup logika gabungan ke tabel target. Jika memungkinkan, buat yang unik.
  • Selain itu, buat indeks pada kolom gabungan dalam tabel target. Jika memungkinkan, indeks berkluster harus unik.
  • Kedua indeks ini memastikan bahwa data dalam tabel diurutkan, dan keunikan membantu kinerja perbandingan. Performa kueri ditingkatkan karena pengoptimal kueri tidak perlu melakukan pemrosesan validasi tambahan untuk menemukan dan memperbarui baris duplikat dan operasi pengurutan tambahan tidak diperlukan.
• Hindari tabel dengan segala bentuk indeks penyimpan kolom sebagai target MERGE pernyataan. Seperti halnya UPDATEs apa pun, Anda mungkin menemukan performa lebih baik dengan indeks penyimpan kolom dengan memperbarui tabel rowstore bertahap, lalu melakukan batch DELETE dan INSERT, alih-alih UPDATE atau MERGE.

Pertimbangan konkurensi untuk MERGE

Dalam hal penguncian, MERGE berbeda dari pernyataan diskrit, berturut-turut INSERT, UPDATE, dan DELETE . MERGE masih menjalankan INSERToperasi , UPDATE, dan DELETE , namun menggunakan mekanisme penguncian yang berbeda. Mungkin lebih efisien untuk menulis pernyataan diskrit INSERT, , UPDATEdan DELETE untuk beberapa kebutuhan aplikasi. Dalam skala besar, MERGE mungkin memperkenalkan masalah konkurensi yang rumit atau memerlukan pemecahan masalah tingkat lanjut. Dengan demikian, rencanakan untuk menguji pernyataan apa pun MERGE secara menyeluruh sebelum menyebarkan ke produksi.

MERGE pernyataan adalah pengganti yang cocok untuk operasi diskrit INSERT, , UPDATEdan DELETE dalam (tetapi tidak terbatas pada) skenario berikut:

• Operasi ETL yang melibatkan jumlah baris besar dijalankan selama waktu ketika operasi bersamaan lainnya tidak* diharapkan. Ketika konkurensi berat diharapkan, logika terpisah INSERT, UPDATE, dan DELETE mungkin berkinerja lebih baik, dengan pemblokiran yang MERGE lebih sedikit, daripada pernyataan.
• Operasi kompleks yang melibatkan jumlah baris kecil dan transaksi tidak mungkin dijalankan selama durasi yang diperpanjang.
• Operasi kompleks yang melibatkan tabel pengguna di mana indeks dapat dirancang untuk memastikan rencana eksekusi yang optimal, menghindari pemindaian tabel dan pencarian demi pemindaian indeks atau - idealnya - pencarian indeks.

Pertimbangan lain untuk konkurensi:

• Dalam beberapa skenario di mana kunci unik diharapkan dimasukkan dan diperbarui oleh MERGE, menentukan HOLDLOCK akan mencegah terhadap pelanggaran kunci unik. HOLDLOCK adalah sinonim untuk SERIALIZABLE tingkat isolasi transaksi, yang tidak memungkinkan transaksi bersamaan lainnya untuk memodifikasi data yang telah dibaca transaksi ini. SERIALIZABLE adalah tingkat isolasi paling aman tetapi memberikan konkurensi paling sedikit dengan transaksi lain yang mempertahankan kunci pada rentang data untuk mencegah baris phantom dimasukkan atau diperbarui saat bacaan sedang berlangsung. Untuk informasi selengkapnya tentang HOLDLOCK, lihat Petunjuk Tabel dan ATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Praktik terbaik JOIN

Untuk meningkatkan performa MERGE pernyataan dan memastikan hasil yang benar diperoleh, kami merekomendasikan panduan gabungan berikut:

• Tentukan hanya kondisi pencarian dalam ON <merge_search_condition> klausul yang menentukan kriteria untuk mencocokkan data dalam tabel sumber dan target. Artinya, tentukan hanya kolom dari tabel target yang dibandingkan dengan kolom tabel sumber yang sesuai.
• Jangan sertakan perbandingan dengan nilai lain seperti konstanta.

Untuk memfilter baris dari tabel sumber atau target, gunakan salah satu metode berikut.

• Tentukan kondisi pencarian untuk pemfilteran baris dalam klausa yang sesuai WHEN . Misalnya: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Tentukan tampilan pada sumber atau target yang mengembalikan baris yang difilter dan mereferensikan tampilan sebagai tabel sumber atau target. Jika tampilan ditentukan pada tabel target, tindakan apa pun terhadapnya harus memenuhi kondisi untuk memperbarui tampilan. Untuk informasi selengkapnya tentang memperbarui data dengan menggunakan tampilan, lihat Mengubah Data Melalui Tampilan.
• WITH <common table expression> Gunakan klausa untuk memfilter baris dari tabel sumber atau target. Metode ini mirip dengan menentukan kriteria pencarian tambahan dalam ON klausul dan mungkin menghasilkan hasil yang salah. Sebaiknya hindari menggunakan metode ini atau uji secara menyeluruh sebelum menerapkannya.

Operasi gabungan dalam MERGE pernyataan dioptimalkan dengan cara yang sama seperti gabungan dalam pernyataan SELECT . Artinya, ketika proses SQL Server bergabung, pengoptimal kueri memilih metode yang paling efisien (dari beberapa kemungkinan) memproses gabungan. Ketika sumber dan target memiliki ukuran yang sama dan panduan indeks yang dijelaskan sebelumnya diterapkan ke tabel sumber dan target, operator gabungan adalah rencana kueri yang paling efisien. Ini karena kedua tabel dipindai sekali dan tidak perlu mengurutkan data. Ketika sumber lebih kecil dari tabel target, operator perulangan berlapis lebih disukai.

Anda dapat memaksa penggunaan gabungan tertentu dengan menentukan OPTION (<query_hint>) klausul dalam MERGE pernyataan. Kami menyarankan agar Anda tidak menggunakan gabungan hash sebagai petunjuk kueri untuk MERGE pernyataan karena jenis gabungan ini tidak menggunakan indeks.

Praktik terbaik parameterisasi

SELECTJika pernyataan , , INSERTUPDATE, atau DELETE dijalankan tanpa parameter, pengoptimal kueri SQL Server mungkin memilih untuk membuat parameter pernyataan secara internal. Ini berarti bahwa setiap nilai harfiah yang terkandung dalam kueri diganti dengan parameter. Misalnya, pernyataan INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), mungkin diimplementasikan secara internal sebagai INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Proses ini, yang disebut parameterisasi sederhana, meningkatkan kemampuan mesin relasional untuk mencocokkan pernyataan SQL baru dengan rencana eksekusi yang ada dan sebelumnya dikompilasi. Performa kueri mungkin ditingkatkan karena frekuensi kompilasi dan kompilasi ulang kueri berkurang. Pengoptimal kueri tidak menerapkan proses parameterisasi sederhana ke MERGE pernyataan. Oleh karena itu, MERGE pernyataan yang berisi nilai harfiah mungkin tidak berkinerja dan INSERTpernyataan individu , UPDATE, atau DELETE karena rencana baru dikompilasi setiap kali MERGE pernyataan dijalankan.

Untuk meningkatkan performa kueri, kami merekomendasikan panduan parameterisasi berikut:

• Parameterisasi semua nilai harfiah dalam ON <merge_search_condition> klausul dan dalam WHEN klausul MERGE pernyataan. Misalnya, Anda dapat menggabungkan pernyataan ke MERGE dalam prosedur tersimpan yang menggantikan nilai harfiah dengan parameter input yang sesuai.
• Jika Anda tidak dapat membuat parameter pernyataan, buat panduan paket jenis TEMPLATE dan tentukan PARAMETERIZATION FORCED petunjuk kueri dalam panduan paket. Untuk informasi selengkapnya, lihat Menentukan Perilaku Parameterisasi Kueri dengan Menggunakan Panduan Paket.
• Jika MERGE pernyataan sering dijalankan pada database, pertimbangkan untuk PARAMETERIZATION mengatur opsi pada database ke FORCED. Berhati-hatilah saat mengatur opsi ini. Opsi ini PARAMETERIZATION adalah pengaturan tingkat database dan memengaruhi bagaimana semua kueri terhadap database diproses. Untuk informasi selengkapnya, lihat Parameterisasi Paksa.
• Sebagai alternatif yang lebih baru dan lebih mudah untuk merencanakan panduan, pertimbangkan strategi serupa dengan petunjuk Query Store. Untuk informasi selengkapnya, lihat Petunjuk Penyimpanan Kueri.

Praktik terbaik klausa TOP

MERGE Dalam pernyataan, TOP klausul menentukan jumlah atau persentase baris yang terpengaruh setelah tabel sumber dan tabel target digabungkan, dan setelah baris yang tidak memenuhi syarat untuk tindakan sisipkan, perbarui, atau hapus dihapus. Klausul TOP selanjutnya mengurangi jumlah baris yang digabungkan ke nilai yang ditentukan dan tindakan sisipkan, perbarui, atau hapus diterapkan ke baris gabungan yang tersisa dengan cara yang tidak diurutkan. Artinya, tidak ada urutan di mana baris didistribusikan di antara tindakan yang ditentukan dalam WHEN klausul. Misalnya, menentukan TOP (10) mempengaruhi 10 baris; dari baris ini, 7 mungkin diperbarui dan 3 disisipkan, atau 1 mungkin dihapus, 5 diperbarui, dan 4 disisipkan dan sebagainya.

Umum untuk menggunakan TOP klausul untuk melakukan operasi bahasa manipulasi data (DML) pada tabel besar dalam batch. Saat menggunakan TOP klausul dalam MERGE pernyataan untuk tujuan ini, penting untuk memahami implikasi berikut.

• Performa I/O mungkin terpengaruh.

  Pernyataan melakukan MERGE pemindaian tabel penuh dari tabel sumber dan target. Membalikan operasi menjadi batch mengurangi jumlah operasi tulis yang dilakukan per batch; namun, setiap batch melakukan pemindaian tabel penuh dari tabel sumber dan target. Aktivitas baca yang dihasilkan dapat memengaruhi performa kueri dan aktivitas bersamaan lainnya pada tabel.

• Hasil yang salah dapat terjadi.

  Penting untuk memastikan bahwa semua batch berturut-turut menargetkan baris baru atau perilaku yang tidak diinginkan seperti salah memasukkan baris duplikat ke dalam tabel target dapat terjadi. Ini dapat terjadi ketika tabel sumber menyertakan baris yang tidak berada dalam batch target tetapi berada di tabel target keseluruhan. Untuk memastikan hasil yang benar:

  • ON Gunakan klausa untuk menentukan baris sumber mana yang memengaruhi baris target yang ada dan yang benar-benar baru.
  • Gunakan kondisi tambahan dalam WHEN MATCHED klausul untuk menentukan apakah baris target sudah diperbarui oleh batch sebelumnya.
  • Gunakan kondisi tambahan dalam WHEN MATCHED klausa dan SET logika untuk memverifikasi baris yang sama tidak dapat diperbarui dua kali.

TOP Karena klausa hanya diterapkan setelah klausul ini diterapkan, setiap eksekusi menyisipkan satu baris yang benar-benar tidak cocok atau memperbarui satu baris yang ada.

Praktik terbaik pemuatan massal

Pernyataan MERGE dapat digunakan untuk memuat data secara massal secara efisien dari file data sumber ke dalam tabel target dengan menentukan OPENROWSET(BULK...) klausul sebagai sumber tabel. Dengan demikian, seluruh file diproses dalam satu batch.

Untuk meningkatkan performa proses penggabungan massal, kami merekomendasikan panduan berikut:

• Buat indeks berkluster pada kolom gabungan dalam tabel target.

• Nonaktifkan indeks non-unik dan tidak berkluster lainnya pada tabel target selama pemuatan MERGEmassal , aktifkan setelahnya. Ini umum dan berguna untuk operasi data massal malam hari.

• ORDER Gunakan petunjuk dan UNIQUE dalam OPENROWSET(BULK...) klausa, untuk menentukan bagaimana file data sumber diurutkan.

  Secara default, operasi massal mengasumsikan file data tidak diurutkan. Oleh karena itu, penting bahwa data sumber diurutkan sesuai dengan indeks terkluster pada tabel target dan bahwa ORDER petunjuk digunakan untuk menunjukkan urutan sehingga pengoptimal kueri dapat menghasilkan rencana kueri yang lebih efisien. Petunjuk divalidasi saat runtime; jika aliran data tidak sesuai dengan petunjuk yang ditentukan, kesalahan akan muncul.

Panduan ini memastikan bahwa kunci gabungan unik dan urutan pengurutan data dalam file sumber cocok dengan tabel target. Performa kueri ditingkatkan karena operasi pengurutan tambahan tidak diperlukan dan salinan data yang tidak perlu tidak diperlukan.

Mengukur dan mendiagnosis performa MERGE

Fitur berikut tersedia untuk membantu Anda mengukur dan mendiagnosis performa MERGE pernyataan.

• Gunakan penghitung stmt penggabungan dalam tampilan manajemen dinamis sys.dm_exec_query_optimizer_info untuk mengembalikan jumlah pengoptimalan kueri yang untuk MERGE pernyataan.
• merge_action_type Gunakan atribut dalam tampilan manajemen dinamis sys.dm_exec_plan_attributes untuk mengembalikan jenis rencana eksekusi pemicu yang MERGE digunakan sebagai hasil dari pernyataan.
• Gunakan Sesi Peristiwa yang Diperluas untuk mengumpulkan data pemecahan masalah untuk pernyataan dengan MERGE cara yang sama seperti yang Anda lakukan untuk pernyataan bahasa manipulasi data (DML) lainnya. Untuk informasi selengkapnya tentang gambaran umum Acara yang Diperluas, lihat Mulai Cepat: Kejadian yang Diperluas dan Menggunakan SSMS XEvent Profiler.

Examples

A. Menggunakan MERGE untuk melakukan operasi INSERT dan UPDATE pada tabel dalam satu pernyataan

Skenario umum adalah memperbarui satu atau beberapa kolom dalam tabel jika ada baris yang cocok. Atau, menyisipkan data sebagai baris baru jika baris yang cocok tidak ada. Anda biasanya melakukan salah satu skenario dengan meneruskan parameter ke prosedur tersimpan yang berisi pernyataan dan UPDATE yang sesuaiINSERT. Dengan pernyataan , MERGE Anda dapat melakukan kedua tugas dalam satu pernyataan. Contoh berikut menunjukkan prosedur tersimpan dalam database AdventureWorks2025 yang berisi INSERT pernyataan dan UPDATE pernyataan. Prosedur ini kemudian dimodifikasi untuk menjalankan operasi yang setara dengan menggunakan satu MERGE pernyataan.

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

B. Menggunakan MERGE untuk melakukan operasi UPDATE dan DELETE pada tabel dalam satu pernyataan

Contoh berikut digunakan MERGE untuk memperbarui ProductInventory tabel dalam database sampel AdventureWorks2025, setiap hari, berdasarkan pesanan yang diproses dalam SalesOrderDetail tabel. Kolom Quantity tabel diperbarui ProductInventory dengan mengurangi jumlah pesanan yang ditempatkan setiap hari untuk setiap produk dalam SalesOrderDetail tabel. Jika jumlah pesanan untuk produk menurunkan tingkat inventori produk menjadi 0 atau kurang, baris untuk produk tersebut ProductInventory dihapus dari tabel.

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. Gunakan MERGE untuk melakukan operasi UPDATE dan INSERT pada tabel target dengan menggunakan tabel sumber turunan

Contoh berikut digunakan MERGE untuk memodifikasi SalesReason tabel dalam database AdventureWorks2025 dengan memperbarui atau menyisipkan baris.

Saat nilai NewName dalam tabel sumber cocok dengan nilai di Name kolom tabel target, (SalesReason), ReasonType kolom diperbarui dalam tabel target. Saat nilai NewName tidak cocok, baris sumber disisipkan ke dalam tabel target. Tabel sumber adalah tabel turunan yang menggunakan konstruktor nilai tabel Transact-SQL untuk menentukan beberapa baris untuk tabel sumber. Untuk informasi selengkapnya tentang menggunakan konstruktor nilai tabel dalam tabel turunan, lihat Konstruktor Nilai Tabel (Transact-SQL).

Klausa OUTPUT dapat berguna untuk mengkueri MERGE hasil pernyataan, untuk informasi selengkapnya, lihat klausa OUTPUT (Transact-SQL). Contohnya juga menunjukkan cara menyimpan hasil OUTPUT klausul dalam variabel tabel. Kemudian, Anda meringkas hasil MERGE pernyataan dengan menjalankan operasi pilih sederhana yang mengembalikan jumlah baris yang disisipkan dan diperbarui.

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. Sisipkan hasil pernyataan MERGE ke tabel lain

Contoh berikut mengambil data yang dikembalikan dari klausul OUTPUT pernyataan dan menyisipkan data tersebut MERGE ke dalam tabel lain. Pernyataan memperbarui MERGEQuantity kolom ProductInventory tabel dalam database AdventureWorks2025, berdasarkan pesanan yang diproses dalam SalesOrderDetail tabel. Contoh mengambil baris yang diperbarui dan menyisipkannya ke dalam tabel lain yang digunakan untuk melacak perubahan inventori.

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

E. Menggunakan MERGE untuk melakukan INSERT atau UPDATE pada tabel edge target dalam database grafik

Dalam contoh ini, Anda membuat tabel Person simpul dan City dan tabel livesInedge . Anda menggunakan MERGE pernyataan di livesIn tepi dan menyisipkan baris baru jika tepi belum ada antara dan PersonCity. Jika tepi sudah ada, maka Anda hanya memperbarui atribut StreetAddress di livesIn tepi.

-- CREATE node and edge tables
CREATE TABLE Person
(
    ID INTEGER PRIMARY KEY,
    PersonName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY,
    CityName VARCHAR(100),
    StateName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE livesIn
(
    StreetAddress VARCHAR(100)
)
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

Konten terkait

• PILIH (Transact-SQL)
• SISIPKAN (Transact-SQL)
• UPDATE (Transact-SQL)
• HAPUS (Transact-SQL)
• Klausa OUTPUT (Transact-SQL)
• MERGE dalam Paket Layanan Integrasi
• Klausa FROM ditambah JOIN, APPLY, PIVOT (Transact-SQL)
• Konstruktor Nilai Tabel (Transact-SQL)