TSQL Merge statement

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 did not use it. That is why I decided to write the following entry, hopefully, you enjoy it.
Microsoft defines 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 delete 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].Newsletter (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

Another merge bug
Optimizing MERGE Statement Performance
MERGE (Transact-SQL)
Sql server 2008 merge more than upsert
Race condition with MERGE