It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of write separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.
Yes that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation perform. That must be ended by semicolon.
Let's see the example using Merge statement.
-- Creating Database
CREATE DATABASE MergeDatabase
GO
USE MergeDatabase
GO
-- Creating tables used for merged operation
IF ( Object_id('UsingTable') > 0 )
DROP TABLE UsingTable
GO
CREATE TABLE UsingTable
(
RefId INT IDENTITY(1, 1),
name VARCHAR(100)
)
GO
IF ( Object_id('TargetTable') > 0 )
DROP TABLE TargetTable
GO
CREATE TABLE TargetTable
(
ChildId INT,
val INT
)
GO
-- Inserting records in both tables
INSERT INTO UsingTable(name)
VALUES ('Target-1'),
('Target-2'),
('Target-3'),
('Target-4'),
('Target-5')
GO
INSERT INTO TargetTable(ChildId,val)
VALUES (1,1),
(2,2),
(3,3),
(6,6)
GO
Let us see how Merge statement works.
1. Merge statement with WHEN MATCHED clause
and updating records
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN MATCHED THEN
UPDATE set val = val + 5 ;
2. Merge statement with WHEN MATCHED clause
and deleting records
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN MATCHED AND ChildId = 3 THEN
DELETE ;
3. Merge statement with WHEN NOT MATCHED BY TARGET clause
and insrting records
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;
4. Merge statement with WHEN NOT MATCHED BY SOURCE clause
and deleting records
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
5. All together at once,
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN MATCHED AND ChildId = 3 THEN
DELETE
WHEN MATCHED THEN
UPDATE set val = val + 5
WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Let's see the result set of TargetTable before and after Merge statement used.
Before Merge statement ran,
After Merge statement ran,
6. Using OUTPUT with Merge statement.
MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)
WHEN MATCHED AND ChildId = 3 THEN
DELETE
WHEN MATCHED THEN
UPDATE set val = val + 5
WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT
$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;
Hope you have already started to use Merge statement.