Prevent Duplicate Rows in Table using Merge in SQL Trigger
SQL Merge statement can help developers to prevent duplicate rows in database tables. This SQL tutorial shows how Transact-SQL Merge command can be used in a SQL Instead Of Insert trigger for maintaining unique combination of selected row columns.
I used Merge in SQL codes of Instead Of Trigger as an alternative method of creating and using UNIQUE Constraint on database table. You will understand it better if you follow the given SQL example codes below.
Let's create a database table first. Our sample database table is named Reservation where we will store meeting rooms, reservation blocks as time blocks and requestors or meeting holders.
Since a meeting room can not be occupied at the same time for different meetings, I can handle this requirement by creating a Unique Constraint.
Within all records of Reservation database table, only one row can have a specific Resource and TimeSlot combination.
If you try to insert a second row with resource and timeslot column values of an existing row, SQL Server unique constraint will raise an error and prevent the addition of the inconsistent data into the database table.
Create Table Reservation (
Id int identity(1,1),
Resource varchar(10),
TimeSlot varchar(20),
Requestor varchar(50)
)
GO
Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)
GO
First table is created then with ALTER TABLE syntax unique constraint is created. For more on SQL Server unique constraint please refer to given SQL tutorial.
SQL Unique Constraint can be a solution for database developers. But since I want to use SQL MERGE command to prevent duplicate data entry, I'll use another method for the solution of this task.
This tutorial combines the use of Merge command in SQL Server and SQL Server Instead Of trigger. You can read more on SQL Merge and SQL Instead Of trigger on referenced SQL tutorials.
To give brief information about Instead Of triggers, they are executed before the DML command (Insert, Update, Delete) instead of the command itself. So an Instead Of Insert trigger will be executed instead of insert operation. This option provides developers to control data manipulation centrally on the target database table.
CREATE TRIGGER dbo.tgReservation ON dbo.Reservation Instead Of INSERT
AS
Merge Reservation
Using (
SELECT
i.Resource,
i.TimeSlot,
i.Requestor
from inserted i
) MergeData ON Reservation.Resource = MergeData.Resource
AND Reservation.TimeSlot = MergeData.TimeSlot
WHEN MATCHED THEN
UPDATE SET Reservation.Requestor = MergeData.Requestor
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (Resource, TimeSlot, Requestor);
GO
Above SQL trigger includes only one statement which is the SQL Merge statement.
If data to be inserted has an equivalent on Reservation target table (considering resource and timeslot columns) are handled in WHEN MATCHED section of the Merge statement.
There is an UPDATE command to be executed for the requestor column in this part.
If there is not a corresponding row already stored in Reservation table for the new data, an INSERT command is executed in WHEN NOT MATCHED BY TARGET section of the Merge command.
To see Merge command in action with sample data will help SQL developers to understand the process easier.
Following data are new for our target SQL database table. I mean they do not contain duplicate rows when only resource and timeslot columns are considered.
You know we have used these columns in SQL Unique Constraint above in this tutorial.
INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'SQL Development Team'
INSERT INTO Reservation SELECT 'Meeting102', '10:00-11:00', 'SQL Server Administration Team'
INSERT INTO Reservation SELECT 'Meeting103', '10:00-11:00', 'SQL Test Team'
Above Insert statements will cause the Instead of trigger tgReservation to be executed. And they will be handled in the WHEN NOT MATCHED BY TARGET sql code block.
But if a duplicate row is being inserted, Merge command will identify it as duplicate. Such duplicate rows are handled in WHEN MATCHED code block and only the requestor information will be used to update existing rows in target table.
Here is a sample duplicate data for Insert operation.
INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'Transact-SQL Training'
Although we execute SQL Insert command with duplicate data, as seen in target database table data, there is no new row added.
On the other hand, previously existing table row has now a new value stored in requestor column.
How did this happen?
Of course, when Insert command is executed the Instead Of Trigger "tgReservation" created on target table is triggered.
The trigger code ran the T-SQL Merge command.
Merge command found a match on table data for the being inserted row.
Finally "WHEN MATCHED THEN" code part of the SQL Server Merge statement is executed:
UPDATE SET Reservation.Requestor = MergeData.Requestor
So using Instead Of Trigger, an Insert statement is converted to an UPDATE statement with MERGE command preventing duplicates in database table.
As one last test row, here is an other unique row for table data. It will be inserted immediately.
INSERT INTO Reservation SELECT 'Meeting103', '11:00-12:00', 'T-SQL Training II'
So as you see, we did not use the Unique Constraint defined on SQL table.
Without the use of constraint, we have completed our task for duplicates with Merge in our SQL development.
Of course, this is not a complete example for this task. A SQL developer should also consider the UPDATE cases.
Programmers can think of using INSTEAD OF INSERT, UPDATE statement for such requirement.
For more samples and information how SQL Merge command can be used, please refer to SQL tutorials: MERGE T-SQL command enhancement in SQL Server 2008 and SQL Server 2008 T-SQL MERGE Statement Example
And also for SQL Server Instead Of Trigger samples, you can read SQL tutorial Last Update Date using SQL Trigger in SQL Server Database Table.