It was about two months I was working on SQL Server 2008 (My main profession) and it’s new features. One of the most interesting features of SQL Server 2008 is the MERGE statement and today I’m going to explain what is it and how it works?
Microsoft teased us with the possibility in prerelease Beta versions of SQL Server 2005, but it yanked the MERGE statement out of T-SQL right before the SQL Server 2005 RTM was released. SQL Server 2008 fixes this situation by finally giving us a fully functional MERGE statement. The MERGE statement is a combination statement that can perform INSERT, UPDATE, and DELETE statements based on whether rows that match the selection criteria exist in the target table or not.
Because it is so flexible, the MERGE statement syntax is a bit more complex than other Data Manipulation Language (DML) statements. It definitely takes some getting used to.
CREATE TABLE #TempShifts
(
Name varchar(50) NOT NULL,
StartTime time NOT NULL,
EndTime time NOT NULL
);
INSERT INTO #TempShifts
(
Name,
StartTime,
EndTime
)
VALUES
(
'Morning Part-time',
'08:00:00',
'12:00:00'
),
(
'Evening Part-time ',
'18:00:00',
'22:00:00'
),
(
'Midnight Swing Shift',
'00:00:00',
'04:00:00'
);
MERGE HumanResources.Shift AS target
USING #TempShifts AS source
ON (target.Name = source.Name)
OR (target.StartTime = source.StartTime
AND target.EndTime = source.EndTime)
WHEN MATCHED THEN
UPDATE
SET
Name = source.Name,
StartTime = source.StartTime,
EndTime = source.EndTime
WHEN TARGET NOT MATCHED THEN
INSERT
(
Name,
StartTime,
EndTime
)
VALUES
(
source.Name,
source.StartTime,
source.EndTime
);
SELECT ShiftID,
Name,
StartTime,
EndTime,
ModifiedDate
FROM HumanResources.Shift;
DROP TABLE #TempShifts;
The MERGE sample begins by creating a temporary table into which you insert names and start and end times for three different work shifts. If you check that out, you’ll notice that one of the new shifts, Evening Part-time, already exists with a start time of 18:00:00 and an end time of 22:00:00. Another shift, Midnight Part-time, exists for the hours between 00:00:00 and 04:00:00. The Morning Part-time shift does not exist at all.
The interesting part of the code sample is the actual MERGE statement itself. This statement begins with the MERGE keyword and the target table. Notice that the table name is aliased, which will make it easier to reference later in the ON clause.
MERGE HumanResources.Shift AS target
The USING clause specifies the data rows to match against the target table. In this case, I’m matching the rows I created in the #TempShifts table against the target HumanResources.Shift table. The USING clause is very important to get right, since the rows returned by this clause determine the actions taken later in the MERGE statement. In this case, I’m simply using the
#TempShifts table as the source, but you can also use a derived table.
USING #TempShifts AS source
The ON clause determines which rows between the target table and the USING clause data rows are matches. This example uses a compound predicate that flags any two rows as matches when they both have either the same Name or the same StartTime and EndTime.
ON (target.Name = source.Name)
OR (target.StartTime = source.StartTime
AND target.EndTime = source.EndTime)
The WHEN MATCHED clause performs its action whenever two rows match based on your ON clause criteria. The action is specified in the THEN clause, and can be either an UPDATE or a DELETE. This example is using the UPDATE clause, which looks like an abbreviated UPDATE statement. Notice that the UPDATE clause does not specify a target table, since the target table is
already specified in the MERGE clause at the beginning of the statement.
WHEN MATCHED THEN
UPDATE
SET Name = source.Name,
StartTime = source.StartTime,
EndTime = source.EndTime
The MERGE statement can have only one WHEN TARGET NOT MATCHED clause. This clause can only perform an INSERT when a row is found in the USING clause source that does not match a row in the target table.
WHEN TARGET NOT MATCHED THEN
INSERT
(
Name,
StartTime,
EndTime
)
VALUES
(
source.Name,
source.StartTime,
source.EndTime
);