Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog contains various articles on the SQL Server. Also this blog contains various interview questions on the SQL Server. Through this blog I try to make the learning of SQL Server/ BI and SQL DBA very easy and in a quick time.

Browse by Tags · View All
CodeProject 6
Sql Server 4
Having Clause 3
Interview Questions on sql server 3
SQLServerPediaSyndication 3
Happy New Year 1
Rebuild And Reorganization of Indexes 1
Replication in SQL Server 1
Fragmentation in SQL Server 1
Log Shipping in SQL Server 1

Archive · View All
February 2012 20
December 2012 5
April 2012 4
August 2012 2
November 2010 2
November 2012 1
June 2012 1
May 2011 1
November 2011 1
October 2011 1

Technology With Vivek Johari

Merge Command In Sql Server

Apr 17 2012 6:15PM by Vivek Johari   



Merge Command:- Merge command is a new feature introduced in the SQL Server 2008. It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.) It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.


The Syntax of the Merge command is given below:-


MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ]
[WHEN NOT MATCHED [BY TARGET]
THEN  ]
[WHEN NOT MATCHED BY SOURCE
THEN  ];


For example, suppose we have two tables in which Import_Employee is a source table and Employee is a target table.


The query for creating the Source table Import_Employee and inserting its data is  given below:-


Create table Import_Employee (Empno int, firstname nvarchar(100), lastname nvarchar(100))

Insert into Import_Employee (Empno, firstname, lastname)

select 1,'Vivek', 'Johari'

Union All

select 2,'Ravi', 'Kumar'

Union All

select 3,'Uma', 'Sharma'

Union All

select 4,'Neha', 'Sharma'


Select * from  Import_Employee






The query for creating the Target table Employee and inserting its data is given below:-


create table Employee(Empno int, fname nvarchar(100), lname nvarchar(100),enddate datetime)


Insert into Employee (Empno, fname, lname)
select 3,'Jagdish', 'Johari'
Union All
select 4,'Neha', 'Sharma'
Union All
select 5,'Sunny', 'Rana'


Select  *  from Employee






Suppose we have the following requirements:-
1) If a empno is exists in both the table then the corresponding values of the column Fname and Lname for the empno in the table Employee is updated by the corresponding values of the column  firstname and  lastname of the Empno from the source table Import_Employee  .
2) If there is no corresponding empno in the Target table Employee for a empno of the table Import_Employee, then the data from the Source table is inserted into the target table for that empno.
3) If there exists a records for a empno in the Target table whose corresponding records are not in the Source table, these these record should be deleted from the target table.


Therefore, the condition on which the target table and Source table is joined will be the Empno.


The merged statement fulfilling all the above three requirements are given below:-


MERGE employee AS TARGET

USING Import_Employee AS SOURCE

ON TARGET.empno=SOURCE.empno

WHEN MATCHED

THEN update set TARGET.fname=SOURCE.firstname,

TARGET.lname = SOURCE.lastname

WHEN NOT MATCHED BY TARGET THEN

insert(empno,fname,lname)

values(SOURCE.empno,SOURCE.firstname,SOURCE.lastname)

WHEN NOT MATCHED BY SOURCE THEN

Delete;

Select * from  Employee 















In the above example, the target table "Employee" and the source table "Import_Employee " has the same empno 3, so the fname and the lname of the table employee is updated with the corresponding  firstname and lastname column value from the table Import_Employee  for empno 3.


Also the table employee does not have the record for the empno 1 and 2. Therefore the corresponding record for the empno 1 and 2 will be inserted from Import_Employee (source) table to the Employee(Target) table.


Again, the target table employee contains the records for the empno 5 whereas the source table Import_Employee  do not contains any records for empno 5 , therefore the record for empno 5 is deleted from the target table by this Merge statement.


So we can see from the above example that with the help of Merge statement, we can perform the update, delete and insert commands within a single statement.



Republished from Technology with Vivek Johari [10 clicks].  Read the original version here [2 clicks].
Vivek Johari
115 · 1% · 445
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect


Submit

Your Comment
Sign Up or Login to post a comment.
"Merge Command In Sql Server" rated 5 out of 5 by 1 readers
Merge Command In Sql Server , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]