I said NO, this confusion is not only with him which has with many SQL buddies. I have finally clarified his doubt with some examples which I would like to share with you all. :)
First of all, what is MERGE statement in SQL Server 2008?
MERGE is combining multiple DML (INSERT, UPDATE, DELETE) actions statements into single action, which improves performance and simplifying transactions. (This option is called UPSERT in other products like ORACLE, Sybase, DB2 etc.,).
Now, Let me explain, how to use MERGE statement instead of our normal DML statements. :)
We will assume that we have a table with the following records.
Sample table creation script :
CREATE TABLE [DBO].[Employee]
(
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_Employee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_Employee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_EEmployee_Active] CHECK ([Active] IN (0,1))
)
GO
Sample records creation script :
INSERT INTO
[DBO].[Employee] ([EmpCode],[EmpName])
VALUES
('E001','Sudhar'),
('E002','Siva'),
('E003','Areef'),
('E004','Jonathan'),
('E005','Matt'),
('E006','Mahesh')
GO
SELECT [EmpCode],[EmpName],[Active] FROM [DBO].[Employee] WITH (NOLOCK)
GO
After executing the above scripts, the output would be :
Performing INSERT and UPDATE operations on a table using single MERGE statement :
Let us see, how to use MERGE statement to update a record. For example I am going to rename the employee's name for employee 'E003'.
DECLARE @Input_EmpCode VARCHAR(10) = 'E003'
DECLARE @Input_EmpName VARCHAR(100) ='Areef Ali Sheik'
DECLARE @Input_Active BIT = 1
MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);
The output would be :
MERGE statement in Stored Procedure :
CREATE PROCEDURE [DBO].[STP_Employee]
(
@Input_EmpCode VARCHAR(10) , @Input_EmpName VARCHAR(100),
@Input_Active BIT = 1
)
AS
SET NOCOUNT ON;
MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);
GO
Execute the below scripts to INSERT or UPDATE records :
EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E003',@Input_EmpName ='Areef Ali Sheik', @Input_Active =1
GO
EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E007',@Input_EmpName ='Sathish', @Input_Active =1
GO
Output would be :
Displaying affected records using MERGE OUTPUT clause statement :
DECLARE @Input_EmpCode VARCHAR(10) = 'E008'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1
MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date;
Recording DML changes into history table using MERGE OUTPUT clause statement :
History table creation script :
CREATE TABLE [DBO].[EmployeeHistory]
(
[Action] VARCHAR(10) NOT NULL,
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL,
[Previous_EmpName] VARCHAR(100) NULL,
[Previous_Active] BIT NULL,
[Modified_Date] DATETIME NOT NULL,
[Modified_By] NCHAR(100) NOT NULL
)
GO
For this example, I am going to update one existing employee and inserting one new employee record.
Updating existing employee :
DECLARE @Input_EmpCode VARCHAR(10) = 'E002'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1
MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_User INTO [EmployeeHistory];
Creating new employee :
DECLARE @Input_EmpCode VARCHAR(10) = 'E009'
DECLARE @Input_EmpName VARCHAR(100) ='Krishna Priya'
DECLARE @Input_Active BIT = 1
MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_By INTO [EmployeeHistory];
Displaying records from history table :
SELECT
[Action],[EmpCode],[EmpName],[Active],[Previous_EmpName],[Previous_Active],[Modified_Date],[Modified_By]
FROM
[EmployeeHistory] WITH (NOLOCK)
GO
As we all know that the above functionality can be achieved using TRIGGERS, but MERGE OUTPUT made us very simple and improves the performance too. :)
Note :
(a). This example will work only in SQL Server 2008 and above versions.
(b). Target Table : The table or view against which the data rows from SOURCE TABLE are matched based on search condition. The target is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.
(c). USING : Specifies the data source that is matched with the data rows in target table based on merge search condition. The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. Table source can be a remote table or a derived table that accesses remote tables.
(d). OUTPUT : Returns a row for every row in TARGET TABLE that is updated, inserted or deleted.
For additional information, please refer http://technet.microsoft.com/en-us/library/bb510625.aspx
very useful info Siva, Thank you!
ReplyDelete