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