Merge Statement SQL Server 2008

MERGE statement is introduced in SQL Server 2008 that can greatly enhance the functionality for Data Warehouse and the staging servers.

The Merge statement allows you to perform DML actions on target table based on whether
or not a row matches information found in source table.

A common Merge scenario is moving data from one table to another.

Like User and UserHistory

User Table :

CREATE TABLE [dbo].[User]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Is_Active] [bit] NULL,
[CREATE_DATE] [date] NOT NULL
)

GO

ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CREATE_DATE] DEFAULT (getdate()) FOR [CREATE_DATE]

GO

AND
UserHistory Table is like:

CREATE TABLE UserHistory
(
HistoryId INT IDENTITY(1, 1),
Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Is_Active BIT NULL,
Create_Date DATE NOT NULL
)

------------------------------------------------------------------------------------

Now insert some data in user table and try running this query more than once and look
on the results that are produced by OUTPUT and $action clauses:

MERGE INTO UserHistory AS UH
USING [User] as U
ON U.Id = UH.Id
WHEN MATCHED THEN
UPDATE SET UH.Id = U.Id, UH.Name = U.Name
,UH.Is_Active = U.Is_Active, UH.Create_date = U.Create_date
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Age, Is_Active, Create_Date)
VALUES (Id, Name, Age, Is_Active, Create_Date)
WHEN NOT MATCHED BY SOURCE THEN
Update SET IS_ACTIVE = 0
Output $action, inserted.*;

-------------------------------------------------------------------------------------
You can perform insert, update and delete operation by matching the Source Table (User)
and the Target Table (UserHistory) in a single operation and can have the details about the
action from OUTPUT clause and using $action you can know the operation performed