TSQL Merge statement

by pluksza · October 10, 2014

SQl Server 2008 offers us an interesting feature called MERGE function that allows you to perform multiple actions in one query. I noticed, however, that many of my fellow programmers never did not use it. That is why I decided to write the following entry, hopefully ou enjoy it.
Microsoft define this function as follows:
“Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table”

Our simple scenario will rely on the fact that we want to add an alert for users who have never logged into our website. In practice, we want to add the appropriate entries to the table [dbo].Newletter (Target).

Now, let’s do some coding, but firstly create two simple tables

CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[LastLoginDate] [datetime] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[NewsLetter](
	[NewsLetterId] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](300) NOT NULL,
	[UserId] [int] NOT NULL,
 CONSTRAINT [PK_NewsLetter] PRIMARY KEY CLUSTERED 
(
	[NewsLetterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NewsLetter]  WITH CHECK ADD  CONSTRAINT [FK_NewsLetter_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO

ALTER TABLE [dbo].[NewsLetter] CHECK CONSTRAINT [FK_NewsLetter_User]
GO

Our schema is fine so let’s get go!

BEGIN TRAN

DELETE [dbo].[Newsletter];
DELETE [dbo].[USER];

INSERT INTO [dbo].[User] ([Name], [LastName], [LastLoginDate]) 
VALUES ('Joe', 'Frazier' ,null);

INSERT INTO [dbo].[User] ([Name], [LastName], [LastLoginDate]) 
VALUES ('Muhammad', 'Ali', GETDATE())

-- Send message to user that didn't logged in
MERGE [dbo].[Newsletter] 
AS TARGET
USING (SELECT UserId, Name, LastName FROM [User] WHERE LastLoginDate is null) AS SOURCE (userid, Name, LastName)
ON TARGET.userId = SOURCE.UserId
WHEN NOT MATCHED by target then
INSERT ([text], [userId])
VALUES ('MESSAGE TO USER '+ SOURCE.Name + ' ' + SOURCE.LastName + ' Portal X Miss You!', SOURCE.userId)

OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN

Output