Today i am clarify about how to Inserting, Updating, and Deleting Data by Using MERGE. In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement in SQL database.
Why we use MERGE statement in SQL?
In a typical data warehousing application, regularly amid the ETL cycle you have to perform INSERT, UPDATE and DELETE operations on a TARGET table by coordinating the records from the SOURCE table. For instance, an items measurement table has data about the items; you have to match up this table with the most recent data about the items from the source table. You would need to compose separate INSERT, UPDATE and DELETE proclamations to revive the objective table with an overhauled item list or do lookups. In spite of the fact that it is by all accounts straight forward at first look, yet it gets to be unwieldy when you have do it all the time or on different tables, even the execution debases fundamentally with this methodology. In this tip we will stroll through how to utilize the MERGE explanation and do this in one pass.
In SQL 2008 you can perform insert, update, or delete operations in a single statement using the MERGE statement . The MERGE statement allows you to join a data source table with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the given operations below.
Operation can perform using MERGE:-
1.According condition insert,update,delete row in target table.If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
2.Synchronize two tables.Insert, update, or delete rows in a target table based on differences with the source table .
Clauses(keyword) using in MERGE Query.
1.MERGE -
The MERGE clause(keyword) specifies the table or view that is the target of the insert, update, or delete operations.
2.USING -
The USING clause(keyword) specifies the data source table being joined with the target table.
3.ON -
The ON clause(keyword) specifies the join conditions that determine where the target table and source table match.
4.WHEN -
The WHEN clause(keyword) (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause(keyword) and any additional search criteria specified in the WHEN clauses.
5.OUTPUT -
The OUTPUT clause(keyword) returns a row for each row in the target table that is inserted, updated, or deleted.
Syntax for MERGE Query:-
MERGE [AS TARGET]
USING [AS SOURCE]
ON
[WHEN MATCHED
THEN ]
[WHEN NOT MATCHED [BY TARGET]
THEN ]
[WHEN NOT MATCHED BY SOURCE
THEN ];
Simple Example with Query:-
Step 1:-Create two table source and target table and insert data .
--Create a target table
CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY )
GO
--Insert records into target table
INSERT INTO Products VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY )
GO
--Insert records into source table
INSERT INTO UpdatedProducts VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO
Step 2:-Now i am going to use MERGE query to perform action on table:-
--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGETUSING UpdatedProducts AS SOURCE ON
(TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate
THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, ProductName, Rate) VALUES
(SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS
TargetProductName, DELETED.Rate AS TargetRate, INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
Step 3:-Run script.
After this script output is showing below in image. There were 2 updates, 1 delete and 1 insert in target table.
If we select all records from the Products(target) table we can see the final results. We can see the Coffee rate was updated from 20.00 to 25.00 in target table , the Muffin rate was updated from 30.00 to 35.00 in target table, Biscuit was deleted and Pizza was inserted inside target table.Result is showing like this.
Description :-
The MERGE SQL statement requires a semicolon (;) as a statement terminator in SQL server database . Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator SQL server database.
- Whenever you used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client in SQL server database.
- In SQL at least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
- Of course it's obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
- MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
- MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS' as we did in previous version of SQL Server.
- For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
