AB-INITO MERGE SQL 2008
MERGE: Multitasking Economical Rapid Going Equations -> Introduced in SQL 2008 as new feature. You would be thinking what abbreviation I have given to this MERGE keyword, but after a lot analysis I found this is the correct definition I can give it. As this performs multiple tasks at time like insert, update, delete in a single statement. Economical, as it reduces some of the tedious work. One of the most important advantages of MERGE statement is that all the data is read and processed only once. This is quite an improvement in performance of database query.
Before MERGE people used to write IF ELSE conditions to perform the task, which on performance basis not very efficient. For e.g.:
— If data identifier (some column having unique value like primary key etc) matches with the parent data table
Then update or delete query
Else
Insert query—–
Here the data is processed every time it performs the insert update or delete separately.
Now using Merge how it works->
This feature allows you to merge two tables together based on a set of criteria. Two tables does not mean that you cannot perform joins, this I will discuss later.
Simple MERGE works on stating a table where all the data should be merged into, the table, query, and the data is coming from and the criteria to decide if they match. Based on this , the query performs on each actions like insert, update, or delete.
MERGE tableName1 AS TARGET
USING(SELECT [COLUMNS] FROM tableName2 ) AS SOURCE
ON TARGET.CRITERIA = SOURCE.CRITERIA
WHEN MATCHED THEN
UPDATE SET
/* UPDATE COLUMNS*/
WHEN MATCHED AND [SOME CONDITION] THEN
DELETE
WHEN NOT MATCHED BY TARGET THEN
INSERT (COLUMNS)
VALUES (SOURCE. [COLUMNS]);
Terminator (semi colon is required at the end) is mandatory after MERGE statement.
Firstly all the match clauses should be mentioned along with conditions then not match clause should be used to achieve better performance.
This is very useful when we are inserting updating bulk data.
Now coming to some typical queries, like can we use joins with MERGE, YES we can use if we didn’t get the direct set criteria we can use joins with SOURE table.
MERGE tableName1 as TARGET
USING( SELECT * FROM tableName2 inner join table3 on tableName2.criteria = table3.criteria) AS SOURCE
ON TARGET.CRITERIA = SOURCE.CRITERIA
WHEN MATCHED THEN
UPDATE
WHEN MATCHED AND CONDITION* THEN
DELETE
WHEN NOT MATCHED THEN
INSERT;
Similarly we can use any number of joins according to our requirements.
In merge like before versions of SQL we can fetch the inserted.ids, updated.ids and deleted.ids and save into some table variable.
Before semicolon we have to take the output into some table variables that can be used further inside the store procedure.
MERGE tableName1 AS TARGET
USING (SELECT [COLUMNS] FROM tableName2 ) AS SOURCE
ON TARGET.CRITERIA = SOURCE.CRITERIA
WHEN NOT MATCHED BY TARGET THEN
INSERT (COLUMNS)
VALUES (SOURCE. [COLUMNS])
Output inserted.ids ,inserted.[columns] into @decaretable ;
For bulk insertion, deletion, and updates we can use user typed table as parameters to the store procedure to be used as SOURCE to perform the task.
More complex and tedious actions can be performed with ease using MERGE statement. MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data just once.
*Any suggestions and questions are appreciated.
Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.
Comments
No comments yet.
Leave a comment