xxxxxxxxxx
The MERGE statement in SQL can be used to perform operations like INSERT, DELETE, and UPDATE, all within the same SQL query. In simple terms, the MERGE statement combines the separate INSERT, UPDATE, and DELETE statements into a single SQL query.
xxxxxxxxxx
MERGE sales.category t
USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = s.category_name,
t.amount = s.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
xxxxxxxxxx
-- Source table
CREATE TABLE SourceTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO SourceTable VALUES (1, 'One'), (2, 'Two');
-- Target table
CREATE TABLE TargetTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO TargetTable VALUES (1, 'Uno'), (3, 'Three');
-- MERGE statement
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)
-- When records are matched, update the records if there is a difference
WHEN MATCHED AND target.Value <> source.Value THEN
UPDATE SET target.Value = source.Value
-- When no records are matched in TargetTable, insert the incoming records from SourceTable
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
-- When no records are matched in SourceTable, delete the record in TargetTable
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- Cleanup
DROP TABLE SourceTable;
DROP TABLE TargetTable;
xxxxxxxxxx
-- Source table
CREATE TABLE SourceTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO SourceTable VALUES (1, 'One'), (2, 'Two');
-- Target table
CREATE TABLE TargetTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO TargetTable VALUES (1, 'Uno'), (3, 'Three');
-- MERGE statement
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)
-- When records are matched, update the records if there is a difference
WHEN MATCHED AND target.Value <> source.Value THEN
UPDATE SET target.Value = source.Value
-- When no records are matched in TargetTable, insert the incoming records from SourceTable
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
-- When no records are matched in SourceTable, delete the record in TargetTable
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- Cleanup
DROP TABLE SourceTable;
DROP TABLE TargetTable;
xxxxxxxxxx
-- Source table
CREATE TABLE SourceTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO SourceTable VALUES (1, 'One'), (2, 'Two');
-- Target table
CREATE TABLE TargetTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO TargetTable VALUES (1, 'Uno'), (3, 'Three');
-- MERGE statement
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)
-- When records are matched, update the records if there is a difference
WHEN MATCHED AND target.Value <> source.Value THEN
UPDATE SET target.Value = source.Value
-- When no records are matched in TargetTable, insert the incoming records from SourceTable
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
-- When no records are matched in SourceTable, delete the record in TargetTable
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- Cleanup
DROP TABLE SourceTable;
DROP TABLE TargetTable;
xxxxxxxxxx
-- Source table
CREATE TABLE SourceTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO SourceTable VALUES (1, 'One'), (2, 'Two');
-- Target table
CREATE TABLE TargetTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO TargetTable VALUES (1, 'Uno'), (3, 'Three');
-- MERGE statement
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)
-- When records are matched, update the records if there is a difference
WHEN MATCHED AND target.Value <> source.Value THEN
UPDATE SET target.Value = source.Value
-- When no records are matched in TargetTable, insert the incoming records from SourceTable
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
-- When no records are matched in SourceTable, delete the record in TargetTable
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- Cleanup
DROP TABLE SourceTable;
DROP TABLE TargetTable;
xxxxxxxxxx
-- Source table
CREATE TABLE SourceTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO SourceTable VALUES (1, 'One'), (2, 'Two');
-- Target table
CREATE TABLE TargetTable (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
INSERT INTO TargetTable VALUES (1, 'Uno'), (3, 'Three');
-- MERGE statement
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)
-- When records are matched, update the records if there is a difference
WHEN MATCHED AND target.Value <> source.Value THEN
UPDATE SET target.Value = source.Value
-- When no records are matched in TargetTable, insert the incoming records from SourceTable
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
-- When no records are matched in SourceTable, delete the record in TargetTable
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- Cleanup
DROP TABLE SourceTable;
DROP TABLE TargetTable;