Saya yakin kita semua pernah memerlukan syncronisasi antara 2 table di SQL, secara umum perintah yang dilakukankan adalah UPSERT (update or Insert), biasanya untuk melakukan ini di perlukan beberapa perintah dan kondisi yang menyebabkan perngaruh yang cukup signifikan dalam logika pemrograman dan tentu saja proses compilasi sintax di SQL server. seperti pada Script perintah di bawah ini
CREATE TABLE MyTable(pk INT PRIMARY KEY, name VARCHAR(10), number INT);
CREATE PROCEDURE Legacy_Upsert_1
@pk INT, @name VARCHAR(10), @number INT
AS
UPDATE MyTable SET name = @name, number = @number WHERE pk = @pk
-- Row is updated even if all the values are correct
IF (@@ROWCOUNT = 0)
INSERT MyTable VALUES(@pk, @name, @number)
-- If row did not exist, a second statement execution is required
CREATE PROCEDURE Legacy_Upsert_2
@pk INT, @name VARCHAR(10), @number INT
AS
-- Updating only if any column needs to be changed requires an extra statement
IF EXISTS (SELECT 1 FROM MyTable WHERE pk = @pk)
UPDATE MyTable
SET name = @name, number = @number
WHERE pk = @pk AND (name <> @name OR number <> @number)
ELSE
INSERT MyTable VALUES(@pk, @name, @number)
kita bisa lihat berapa perintah TSQL perlu kita tuliskan di SQL server untuk menghasilkan data yang jika data sudah ada di MyTable maka akan di update dan jika data belum ada maka akan di insert ke MyTable.
Alasan utama kenapa kita akan sangat terbantu dengan intruksi merge ini diantaranya :
-
Pada proses OLTP sangat membantu dalam proses UP-SERT (Update or Insert), Biasanya di gunakan dalam proses merging (penggabungan data) jika row data ada di table target maka akan di update jika belum ada maka akan di insert
-
Pada proses melakukan syncronisasi data dari 2 table, Proses Insert/Update/Delete pada table target berdasarkan perbedaan data dari Table Source
-
Melakukan Tracking history dari SCD (slowly Changing Dimension pada Data Warehouse), Update Jika data di target berbeda dengan source, insert jika data adalah data baru
-
Tracking inventory, seperti pada proses inventory operasi Insert Stock baru, update existing stock, Delete jika Amount = 0
Dengan menggunakan Merge Statement ini kita bisa menggabungkan perintah DML (insert/update/delete) dengan hanya menggunakan 1 statement yaitu MERGE, operasinya sendiri merupakan hasil Join antara Table Source dengan table Target.
Berikut Syntax perintah Merge:
[ WITH <common_table_expression> [ ,…n ] ]
MERGE
[ TOP (expression) [ PERCENT ] ]
[ INTO ] <target_table> [ [ AS ] table_alias ] [ WITH( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
<merge_clause> [ …n ]
[ OUTPUT <dml_select_list> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;
<merge_clause>:=
{
WHEN MATCHED [ AND <search_condition> ]
THEN { UPDATE SET <set_clause> | DELETE }
| WHEN [ TARGET ] NOT MATCHED [ AND <search_condition> ]
THEN INSERT [ (column_list) ]
{ VALUES (values_list) | DEFAULT VALUES }
| WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
THEN { UPDATE SET <set_clause> | DELETE }
}
Berikut contoh penggunaan perintah Merge
CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
MERGE Stock S
USING Trades T
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN
DELETE -- delete stock if entirely sold
WHEN MATCHED THEN
-- delete takes precedence on update
UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN
INSERT VALUES (Stock, Delta);
-- BOEING is deleted, GE inserted, MSFT updated
Dari perintah diatas kita bisa lihat operasi Merge pada table Stock dan Trade, dimana jika nilai trades menyebabkan nilai di stock habis maka data di stock di hapus, jika di stock ada maka data akan di update dengan menambahkan nilai Qty dengan nilai Delta sedangkan jika belum ada maka akan di insert data stock baru.
Klausa penting dalam perintah Merge
- WHEN MATCH, Jika row data ada di Table Source dan Table Target ini sama juga dengan operasi "Source Inner Join Target". Maka Valid Actionnya adalah UPDATE atau DELETE
- WHEN TARGET NOT MATCH, jika row data di table source tidak ada di table target ini sama juga dengan operasi "Source Left Outer Join Target". maka Valid Actionnya adalah INSERT
- WHEN SOURCE NOT MATCH, jika row data di table Target tidak ada di table source ini sama juga dengan operasi "Source Right Outer Join Target". maka Valid Actionnya adalah UPDATE atau DELETE
Selamat mencoba