MERGE (Transact-SQL)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure Synapse Analytics

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

MERGE saat ini dalam pratinjau untuk Azure Synapse Analytics. Ubah pemilih versi produk untuk konten penting di MERGE khusus untuk Azure Synapse Analytics. Untuk mengubah versi dokumen ke Azure Synapse Analytics: Azure Synapse Analytics.

Catatan

MERGE saat ini dalam pratinjau untuk Azure Synapse Analytics. Fitur pratinjau dimaksudkan untuk pengujian saja dan tidak boleh digunakan pada instans produksi atau data produksi. Sebagai fitur pratinjau, MERGE dapat mengalami perubahan perilaku atau fungsionalitas. Simpan juga salinan data pengujian Anda jika data tersebut penting.

Tips Performa: 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 dasar. 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

-- SQL Server and 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 . ]  
  target_table  
}  

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

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

-- MERGE (Preview) for Azure Synapse Analytics 
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ INTO ] <target_table> [ [ 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 ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error. 

Argumen

DENGAN <common_table_expression>

Menentukan kumpulan hasil atau tampilan 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 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 klausa WHEN. Misalnya, menentukan TOP (10) memengaruhi 10 baris. Dari baris ini, 7 dapat diperbarui dan 3 disisipkan, atau 1 dapat dihapus, 5 diperbarui, dan 4 disisipkan, dan sebagainya.

Tanpa filter pada tabel sumber, pernyataan MERGE dapat melakukan pemindaian tabel atau pemindaian indeks berkluster pada tabel sumber, serta pemindaian tabel atau pemindaian indeks berkluster dari tabel target. Oleh karena itu, performa I/O terkadang terpengaruh bahkan saat 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.

nama_skema

Nama skema tempat target_table berada.

target_table

Tabel atau tampilan tempat baris data dari <table_source> dicocokkan berdasarkan <clause_search_condition>. target_table adalah target dari 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.

Petunjuk dapat ditentukan sebagai <merge_hint>.

Perhatikan bahwa merge_hints tidak didukung untuk Azure Synapse Analytics.

[ AS ] table_alias

Nama alternatif untuk mereferensikan tabel untuk target_table.

MENGGUNAKAN <table_source>

Menentukan sumber data yang cocok dengan baris data dalam target_table berdasarkan <kondisi> merge_search. 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.

<> table_source bisa menjadi tabel turunan SELECT ... UNION ALL yang menggunakan untuk membuat tabel dengan menentukan beberapa baris.

[ AS ] table_alias

Nama alternatif untuk mereferensikan tabel untuk table_source.

Untuk informasi selengkapnya tentang sintaks dan argumen klausul 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 terkait dari tabel sumber. 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 <klausa merge_matched> .

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

KETIKA TIDAK COCOK DENGAN SUMBER, 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 <klausa merge_matched> .

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 seseorang harus menentukan tindakan UPDATE dan satu harus menentukan tindakan DELETE. Hanya kolom dari tabel target yang dapat dirujuk dalam <clause_search_condition>.

Saat tidak ada baris yang dikembalikan oleh <table_source>, kolom dalam tabel sumber tidak dapat diakses. Jika tindakan perbarui atau hapus yang ditentukan dalam <klausa merge_matched> mereferensikan kolom dalam tabel sumber, kesalahan 207 (Nama kolom tidak valid) dikembalikan. Misalnya, klausul 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 diperbolehkan. 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 seperti 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', 'UPDATE', atau '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 klausul 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 mana 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 klausa ini, lihat UPDATE (Transact-SQL). Mengatur variabel ke nilai yang sama dengan kolom tidak didukung.

HAPUS

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 akan gagal. 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 disisipkan ke dalam tabel target. Ekspresi tidak boleh berisi pernyataan EXECUTE.

NILAI DEFAULT

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

Untuk informasi selengkapnya tentang klausul 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

Catatan

Di Azure Synapse Analytics, perintah MERGE (pratinjau) memiliki perbedaan berikut dibandingkan dengan server SQL dan database Azure SQL.

• Menggunakan MERGE untuk memperbarui kolom kunci distribusi tidak didukung. Untuk solusinya, gunakan UPDATE FROM ... JOIN pernyataan untuk menyinkronkan dua tabel dan memperbarui kunci distribusi.
• Pembaruan MERGE diimplementasikan sebagai pasangan hapus dan sisipkan. Jumlah baris yang terpengaruh untuk pembaruan MERGE menyertakan baris yang dihapus dan disisipkan.
• MENGGABUNGKAN... KETIKA TIDAK COCOK INSERT tidak didukung untuk tabel dengan kolom IDENTITY.
• Konstruktor nilai tabel tidak dapat digunakan dalam klausa USING untuk tabel sumber. Gunakan SELECT ... UNION ALL untuk membuat tabel sumber turunan dengan beberapa baris.
• Dukungan untuk tabel dengan jenis distribusi yang berbeda dijelaskan dalam tabel ini:

KLAUSUL MERGE di analitik Azure Synapse	Tabel distribusi TARGET yang didukung	Tabel distribusi SUMBER yang didukung	Komentar
KETIKA DICOCOKKAN	Semua jenis distribusi	Semua jenis distribusi
TIDAK COCOK DENGAN TARGET	HASH	Semua jenis distribusi	Gunakan PERBARUI/HAPUS DARI... JOIN untuk menyinkronkan dua tabel.
TIDAK COCOK DENGAN SUMBER	Semua jenis distribusi	Semua jenis distribusi

Tip

Jika Anda menggunakan kunci hash distribusi sebagai kolom JOIN di MERGE dan hanya melakukan perbandingan kesetaraan, Anda dapat menghilangkan kunci distribusi dari daftar kolom dalam WHEN MATCHED THEN UPDATE SET klausul, karena ini adalah pembaruan redundan.

Penting

Dalam Azure Synapse Analytics, perintah MERGE, yang saat ini dalam pratinjau, mungkin, dalam kondisi tertentu, membiarkan tabel target dalam keadaan tidak konsisten, dengan baris ditempatkan dalam distribusi yang salah, menyebabkan kueri nanti mengembalikan hasil yang salah dalam beberapa kasus. Masalah ini dapat terjadi dalam 2 kasus:

Skenario	Komentar
Kasus 1 Menggunakan MERGE pada tabel TARGET terdistribusi HASH yang berisi indeks sekunder atau batasan UNIQUE.	- Diperbaiki di Synapse SQL versi 10.0.15563.0 dan yang lebih tinggi. - Jika SELECT @@VERSION mengembalikan versi yang lebih rendah dari 10.0.15563.0, jeda dan lanjutkan kumpulan Synapse SQL secara manual untuk mengambil perbaikan ini. - Hingga perbaikan diterapkan ke kumpulan Synapse SQL Anda, hindari menggunakan perintah MERGE pada tabel TARGET terdistribusi HASH yang memiliki indeks sekunder atau batasan UNIK.
Kasus 2 Menggunakan MERGE untuk memperbarui kolom kunci distribusi tabel terdistribusi HASH.	- Jangan gunakan MERGE untuk memperbarui kolom kunci distribusi karena ini tidak didukung. - Pastikan kumpulan Synapse SQL Anda berada pada versi 10.0.15658.0 dan yang lebih tinggi.

Perhatikan bahwa pembaruan dalam kedua skenario tidak memperbaiki tabel yang sudah terpengaruh oleh eksekusi MERGE sebelumnya. Gunakan skrip di bawah ini untuk mengidentifikasi dan memperbaiki tabel yang terpengaruh secara manual.

Untuk memeriksa tabel terdistribusi hash mana dalam database yang mungkin menjadi perhatian (jika digunakan dalam Kasus di atas), jalankan pernyataan ini

-- Case 1
select a.name, c.distribution_policy_desc, b.type from sys.tables a join sys.indexes b
on a.object_id = b.object_id
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where b.type = 2 and c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
select a.name, c.distribution_policy_desc from sys.tables a 
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where c.distribution_policy_desc = 'HASH';

Untuk memeriksa apakah tabel terdistribusi hash untuk MERGE dipengaruhi oleh Kasus 1 atau Kasus 2, ikuti langkah-langkah ini untuk memeriksa apakah tabel memiliki baris yang mendarat dalam distribusi yang salah. Jika 'tidak perlu diperbaiki' dikembalikan, tabel ini tidak terpengaruh.

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

create table [check_table_1] with(distribution = round_robin) as
select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> group by <DISTRIBUTION_COLUMN>;
go

create table [check_table_2] with(distribution = hash(x)) as
select x from [check_table_1];
go

if not exists(select top 1 * from (select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> except select x from 
[check_table_2]) as tmp)
select 'no need for repair' as result
else select 'needs repair' as result
go

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

Untuk memperbaiki tabel yang terpengaruh, jalankan pernyataan ini untuk menyalin semua baris dari tabel lama ke tabel baru.

if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go
if object_id('[repair_table]', 'U') is not null
drop table [repair_table];
go
create table [repair_table_temp] with(distribution = round_robin) as select * from <MERGE_TABLE>;
go

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
create table [repair_table] with(distribution = hash(<DISTRIBUTION_COLUMN>)) as select * from [repair_table_temp];
go
if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go

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 atasnya, 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 sepenuhnya dicadangkan ketika tingkat kompatibilitas database diatur ke 100 atau lebih tinggi. Pernyataan MERGE tersedia di bawah tingkat kompatibilitas database 90 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 pemicu terlebih dahulu atau terakhir. Pemicu yang ditentukan untuk tindakan yang sama mematuhi urutan yang Anda tentukan. Untuk informasi selengkapnya tentang mengatur urutan pengaktifan pemicu, lihat Menentukan Pemicu Pertama dan Terakhir.

Jika tabel target memiliki pemicu ALIH-ALIH DIAKTIFKAN yang ditentukan di dalamnya 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 pemicu ALIH-ALIH PEMBARUAN atau ALIH-ALIH HAPUS ditentukan pada target_table, operasi pembaruan atau penghapusan tidak dijalankan. Sebaliknya, pemicu diaktifkan dan tabel yang dimasukkan dan dihapus kemudian mengisi yang sesuai.

Jika ada PEMicu ALIH-ALIH INSERT yang ditentukan pada target_table, operasi penyisipan tidak dilakukan. Sebagai gantinya, tabel akan diisi 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 ditangkap 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, 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, itu harus unik.
  • Selain itu, buat indeks pada kolom gabungan dalam tabel target. Jika memungkinkan, itu harus menjadi indeks berkluster yang unik.
  • Kedua indeks ini memastikan bahwa data dalam tabel diurutkan, dan keunikan membantu performa perbandingan. Performa kueri ditingkatkan karena pengoptimal kueri tidak perlu melakukan pemrosesan validasi ekstra 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 yang di-batch, bukan 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 dapat memperkenalkan masalah konkurensi yang rumit atau memerlukan pemecahan masalah tingkat lanjut. Dengan demikian, rencanakan untuk menguji pernyataan MERGE secara menyeluruh sebelum menyebarkan ke produksi.

Pernyataan MERGE adalah pengganti yang cocok 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 dapat berkinerja lebih baik, dengan lebih sedikit pemblokiran, daripada pernyataan MERGE.
• Operasi kompleks yang melibatkan jumlah baris kecil dan transaksi tidak mungkin dijalankan untuk 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 pembacaan sedang berlangsung. Untuk informasi selengkapnya tentang HOLDLOCK, lihat Petunjuk dan SET 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 klausa ON dan dapat 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 berukuran 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>) klausa 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 dapat 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), dapat 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 dapat 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 dilakukan dan pernyataan INSERT, UPDATE, atau DELETE individual karena rencana baru dikompilasi setiap kali pernyataan MERGE dijalankan.

Untuk meningkatkan performa kueri, kami merekomendasikan panduan parameterisasi berikut:

• Parameterkan semua nilai harfiah dalam ON <merge_search_condition> klausul dan dalam WHEN klausa pernyataan MERGE. Misalnya, Anda dapat memasukkan pernyataan MERGE ke dalam prosedur tersimpan yang menggantikan nilai literal 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 rencana. 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 PARAMETERIZATION ini 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 Penyimpanan Kueri. 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 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 klausa WHEN. Misalnya, menentukan TOP (10) memengaruhi 10 baris; dari baris ini, 7 dapat diperbarui dan 3 dimasukkan, atau 1 dapat dihapus, 5 diperbarui, dan 4 dimasukkan dan sebagainya.

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

• Performa I/O mungkin terpengaruh.

  Pernyataan MERGE melakukan pemindaian tabel lengkap dari tabel sumber dan target. Membaangan operasi menjadi batch mengurangi jumlah operasi tulis yang dilakukan per batch; namun, setiap batch akan 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 ada dalam batch target tetapi berada dalam 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 telah 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 klausa 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.

• Gunakan petunjuk ORDER dan UNIQUE dalam OPENROWSET(BULK…) klausul 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 berkluster 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 dalam mengukur dan mendiagnosis performa pernyataan MERGE.

• Gunakan penghitung stmt gabungan 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 dari pernyataan MERGE.
• Gunakan Sesi Acara 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 Acara yang Diperluas, lihat Mulai Cepat: Acara yang diperluas di SQL Server dan SSMS XEvent Profiler.

Contoh

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 UPDATE dan INSERT yang sesuai. Dengan pernyataan MERGE, Anda dapat melakukan kedua tugas dalam satu pernyataan. Contoh berikut menunjukkan prosedur tersimpan dalam database AdventureWorks2012 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  

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.
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);  
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';  

-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');  
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 AdventureWorks2012, 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 menghilangkan 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  
    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';  

CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS tgt  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
    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();  
GO  

EXECUTE Production.usp_UpdateInventory '20030501';  

C. Menggunakan 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 AdventureWorks2012 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 Klausul OUTPUT. Contoh ini juga menunjukkan cara menyimpan hasil klausa OUTPUT dalam variabel tabel. Lalu, 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;  

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 SELECT ... UNION ALL untuk menentukan beberapa baris untuk tabel sumber.

MERGE INTO Sales.SalesReason AS tgt  
USING (SELECT 'Recommendation','Other' UNION ALL 
       SELECT 'Review', 'Marketing' UNION ALL 
       SELECT '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);  

D. Menyisipkan 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 AdventureWorks2012, 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  
            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 PersonCity. Jika tepi sudah ada, maka Anda cukup memperbarui atribut StreetAddress di tepi livesIn .

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

Lihat juga

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• Klausul OUTPUT (Transact-SQL)
• BERGABUNG dalam Paket SSIS
• FROM (Transact-SQL)
• Konstruktor Nilai Tabel (Transact-SQL)

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• BERGABUNG dalam Paket SSIS
• FROM (Transact-SQL)
• Konstruktor Nilai Tabel (Transact-SQL)