T-SQL: Merge Simplifies Development: Listing 1

The MERGE statement uses the contents of a table-valued variable and updates a persistent table in the database. The USING clause specifies the source of the update data. The WHEN MATCHED clause specifies that if the record already exists in the target table, it should execute the included UPDATE statement. The WHEN NOT MATCHED clause lets you insert the data when there is no matching record. There are various other forms of the MATCHED clause available in the MERGE statement to handle deletions and various scenarios.

CREATE TYPE dbo.typDogUpdates AS TABLE (
    dogID INT NOT NULL,
    Name NVARCHAR(25) NOT NULL,
    BirthDate DATE NULL,
    DeathDate DATE NULL,
    Weight INT NULL,
    HarnessSize NVARCHAR(10)
)
GO

DECLARE @dogUpdates AS dbo.typDogUpdates

INSERT INTO @dogUpdates(dogID, Name, 
    BirthDate, DeathDate, Weight, HarnessSize)
VALUES
    (2, ‘Mardy', ‘6/30/1997', NULL, 62, ‘Yellow'),
    (3, ‘Izzi', ‘6/30/2001', NULL, 39, ‘RedGreen'),
    (5, ‘Raja', NULL, NULL, 42, ‘RedGreen');

SELECT * FROM dbo.Dogs

MERGE dbo.Dogs AS Target
USING (SELECT dogID, Name, Birthdate, 
    DeathDate, Weight, HarnessSize 
    FROM @dogUpdates) AS Source
    ON (Target.dogID = Source.dogID)
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name, 
        Target.BirthDate = Source.BirthDate, 
        Target.DeathDate = Source.DeathDate,
        Target.Weight = Source.Weight,
        Target.HarnessSize = Source.HarnessSize
WHEN NOT MATCHED THEN
    INSERT (dogID, Name, Birthdate, 
        DeathDate, Weight, HarnessSize)
    VALUES (Source.dogID, Source.Name, 
        Source.Birthdate, Source.DeathDate, 
        Source.Weight, Source.HarnessSize)
OUTPUT $action, Inserted.*, Deleted.*;

SELECT * FROM dbo.Dogs
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.