MERGE (Transact-SQL)

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

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.

Catatan

Untuk informasi MERGE khusus untuk Azure Synapse Analytics, ubah pilihan versi ke Azure Synapse Analytics.

Tip

Perilaku bersyarat yang dijelaskan untuk pernyataan MERGE 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 pernyataan INSERT, 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);

Konvensi sintaks transact-SQL

Sintaks

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>

Argumen

WITH <common_table_expression>

Menentukan kumpulan atau tampilan hasil bernama sementara, juga dikenal sebagai ekspresi tabel umum, yang ditentukan dalam cakupan pernyataan MERGE. Kumpulan hasil berasal dari kueri sederhana dan dirujuk oleh pernyataan MERGE. 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 ekspresi TOP 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. Klausa TOP semakin 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 klausul WHEN. 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, pernyataan MERGE 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 klausul TOP 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 klausa WHEN dari pernyataan MERGE.

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

[ AS ] 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 klausa WHEN dari pernyataan MERGE. <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.

[ AS ] table_alias

Nama alternatif untuk mereferensikan tabel untuk table_source.

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

ON <merge_search_condition>

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

Perhatian

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 klausa ON; 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 MERGE dapat memiliki, paling banyak, dua klausa WHEN MATCHED. Jika dua klausa ditentukan, klausul pertama harus disertai dengan klausa AND <search_condition> . Untuk baris tertentu, klausa WHEN MATCHED kedua hanya diterapkan jika yang pertama tidak. Jika ada dua klausa WHEN MATCHED, satu harus menentukan tindakan UPDATE dan satu harus menentukan tindakan DELETE. 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 AKTIF <merge_search_condition> yang tidak cocok dengan <table_source> baris dalam target_table, tetapi memenuhi kondisi pencarian tambahan, jika ada. Nilai yang akan disisipkan ditentukan oleh <merge_not_matched> klausa. Pernyataan MERGE hanya dapat memiliki satu klausa WHEN NOT MATCHED [ BY TARGET].

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 MERGE dapat memiliki paling banyak dua klausul WHEN NOT MATCHED BY SOURCE. Jika dua klausa ditentukan, maka klausul pertama harus disertai dengan klausa AND <clause_search_condition> . Untuk baris tertentu, klausa WHEN NOT MATCHED BY SOURCE kedua hanya diterapkan jika yang pertama tidak. Jika ada dua klausa WHEN NOT MATCHED BY SOURCE, maka satu harus menentukan tindakan UPDATE dan satu harus menentukan tindakan DELETE. 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 pernyataan MERGE. Kata kunci WITH dan tanda kurung diperlukan.

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

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

Perhatian

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

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 Klausul OUTPUT (Transact-SQL).

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

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

<merge_matched>

Menentukan tindakan perbarui atau hapus yang diterapkan ke semua baris target_table yang tidak cocok dengan baris yang dikembalikan oleh <table_source> AKTIF <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 MERGE gagal. column_list harus diapit tanda kurung dan dibatasi oleh koma.

VALUES ( values_list )

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

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 klausul 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).

Keterangan

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

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

Pernyataan MERGE memerlukan titik koma (;) sebagai terminator pernyataan. Kesalahan 10713 muncul ketika pernyataan MERGE 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 tersedia di bawah 90 tingkat kompatibilitas database dan 100 ; namun, kata kunci tidak sepenuhnya dicadangkan ketika tingkat kompatibilitas database diatur ke 90.

Perhatian

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

Implementasi pemicu

Untuk setiap tindakan sisipkan, perbarui, atau hapus yang ditentukan dalam pernyataan MERGE, SQL Server mengaktifkan pemicu AFTER yang sesuai yang ditentukan pada tabel target, tetapi tidak menjamin tindakan mana yang akan diaktifkan memicu 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 ALIH-ALIH diaktifkan yang ditentukan untuk tindakan sisipkan, perbarui, atau hapus yang dilakukan oleh pernyataan MERGE, itu harus memiliki pemicu ALIH-ALIH diaktifkan untuk semua tindakan yang ditentukan dalam pernyataan MERGE.

Jika ada PEMicu ALIH-ALIH PEMBARUAN atau ALIH-ALIH HAPUS didefinisikan 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 PEMicu INSERT ALIH-ALIH ditentukan pada target_table, operasi penyisipan tidak dilakukan. Sebagai gantinya, tabel akan diisi dengan sesuai.

Catatan

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

Izin

Memerlukan izin SELECT pada tabel sumber dan izin INSERT, UPDATE, atau DELETE pada tabel target. Untuk informasi selengkapnya, lihat bagian Izin di artikel SELECT, INSERT, UPDATE, dan DELETE .

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 pernyataan MERGE, 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 pernyataan MERGE. Seperti halnya UPDATEs apa pun, Anda mungkin menemukan performa yang lebih baik dengan indeks penyimpan kolom dengan memperbarui tabel rowstore bertahap, lalu melakukan DELETE dan INSERT batch, alih-alih UPDATE atau MERGE.

Pertimbangan konkurensi untuk MERGE

Dalam hal penguncian, MERGE berbeda dari pernyataan INSERT, UPDATE, dan DELETE yang diskrit, berturut-turut. MERGE masih menjalankan operasi INSERT, UPDATE, dan DELETE, namun menggunakan mekanisme penguncian yang berbeda. Mungkin lebih efisien untuk menulis pernyataan INSERT, UPDATE, dan DELETE diskrit 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 MERGE apa pun secara menyeluruh sebelum menyebarkan ke produksi.

Pernyataan MERGE adalah pengganti yang sesuai untuk operasi INSERT, UPDATE, dan DELETE diskrit 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 INSERT, UPDATE, dan DELETE terpisah mungkin berkinerja lebih baik, dengan pemblokiran yang lebih sedikit, daripada pernyataan MERGE.
• 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 pelanggaran kunci unik. HOLDLOCK adalah sinonim untuk tingkat isolasi transaksi SERIALIZABLE, 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 dan ATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Praktik terbaik JOIN

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

• Tentukan hanya kondisi pencarian dalam klausa ON <merge_search_condition> 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 WHEN yang sesuai. 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 Memodifikasi 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 klausul ON dan mungkin menghasilkan hasil yang salah. Sebaiknya hindari menggunakan metode ini atau uji secara menyeluruh sebelum menerapkannya.

Operasi gabungan dalam pernyataan MERGE 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 pernyataan MERGE. Kami menyarankan agar Anda tidak menggunakan gabungan hash sebagai petunjuk kueri untuk pernyataan MERGE karena jenis gabungan ini tidak menggunakan indeks.

Praktik terbaik parameterisasi

Jika pernyataan SELECT, INSERT, UPDATE, 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 pernyataan MERGE. Oleh karena itu, pernyataan MERGE yang berisi nilai harfiah mungkin tidak dijalankan dan pernyataan INSERT, UPDATE, atau DELETE individual karena paket baru dikompilasi setiap kali pernyataan MERGE dijalankan.

Untuk meningkatkan performa kueri, kami merekomendasikan panduan parameterisasi berikut:

• Parameterisasi semua nilai harfiah dalam ON <merge_search_condition> klausul dan dalam WHEN klausa pernyataan MERGE. Misalnya, Anda dapat menggabungkan pernyataan MERGE ke 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 pernyataan MERGE sering dijalankan pada database, pertimbangkan untuk mengatur opsi PARAMETERISASI 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

Dalam pernyataan MERGE, klausa TOP 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. Klausa TOP semakin 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 klausul WHEN. Misalnya, menentukan TOP (10) memengaruhi 10 baris; dari baris ini, 7 mungkin diperbarui dan 3 disisipkan, atau 1 mungkin dihapus, 5 diperbarui, dan 4 dimasukkan dan sebagainya.

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

• Performa I/O mungkin terpengaruh.

  Pernyataan MERGE melakukan 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:

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

Karena klausul TOP 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...) klausa 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 MERGE beban massal, aktifkan setelahnya. Ini umum dan berguna untuk operasi data massal malam hari.

• Gunakan petunjuk ORDER 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 petunjuk ORDER 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 pernyataan MERGE.

• Gunakan penghitung stmt penggabungan dalam tampilan manajemen dinamis sys.dm_exec_query_optimizer_info untuk mengembalikan jumlah pengoptimalan kueri yang untuk pernyataan MERGE.
• merge_action_type Gunakan atribut dalam tampilan manajemen dinamis sys.dm_exec_plan_attributes untuk mengembalikan jenis rencana eksekusi pemicu yang digunakan sebagai hasil pernyataan MERGE.
• Gunakan Sesi Peristiwa yang Diperluas untuk mengumpulkan data pemecahan masalah untuk pernyataan MERGE dengan 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.

Contoh

J. 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 UPDATE dan INSERT yang sesuai. Dengan pernyataan MERGE, Anda dapat melakukan kedua tugas dalam satu pernyataan. Contoh berikut menunjukkan prosedur tersimpan dalam database AdventureWorks2022 yang berisi pernyataan INSERT dan pernyataan UPDATE. Prosedur ini kemudian dimodifikasi untuk menjalankan operasi yang setara dengan menggunakan satu pernyataan MERGE.

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 menggunakan MERGE untuk memperbarui ProductInventory tabel dalam database sampel AdventureWorks2022, 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 menggunakan MERGE untuk mengubah SalesReason tabel dalam database AdventureWorks2022 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 hasil pernyataan MERGE, untuk informasi selengkapnya, lihat Klausa OUTPUT. Contoh ini juga menunjukkan cara menyimpan hasil klausa OUTPUT dalam variabel tabel. Kemudian, Anda meringkas hasil pernyataan MERGE 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 MERGE dan menyisipkan data tersebut ke dalam tabel lain. Pernyataan MERGE memperbarui Quantity kolom ProductInventory tabel dalam database AdventureWorks2022, 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 pernyataan MERGE di livesIn tepi dan menyisipkan baris baru jika tepi belum ada antara dan Person City. 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

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• Klausul OUTPUT (Transact-SQL)
• MERGE dalam Paket Layanan Integrasi
• FROM (Transact-SQL)
• Konstruktor Nilai Tabel (Transact-SQL)