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