SQL SERVER -- MERGE -- Part-6

MERGE:-
                                               MERGE command is used to perform all INSERT,UPDATE and DELETE operations at a time.

-- CREATE FOLLOWING TWO TABLES AND INSERT SOME SAMPLE DATA

USE SQLSERVER

GO

-- CREATE SOURCE TABLE

CREATE TABLE EMP_SRC

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

       ,IsACTIVE BIT

)

-- INSERT SOURCE TABLE DATA

INSERT INTO EMP_SRC

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101,1)

INSERT INTO EMP_SRC

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104,1)

INSERT INTO EMP_SRC

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105,1)

-- CREATE TARGET TABLE

CREATE TABLE EMP_TGT

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

       ,IsACTIVE BIT

)

-- INSERT TARGET TABLE DATA

INSERT INTO EMP_TGT

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101,1)

INSERT INTO EMP_TGT

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104,1)

INSERT INTO EMP_TGT

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105,1)

-- MERGE

MERGE EMP_TGT AS T

USING EMP_SRC AS S

ON S.EMPID=T.EMPID

WHEN MATCHED AND S.ISACTIVE=0

THEN DELETE

WHEN MATCHED THEN

UPDATE SET T.EMPNAME=S.EMPNAME

         ,T.SAL= S.SAL

            ,T.DOJ= S.DOJ

            ,T.LOC= S.LOC

            ,T.GENDER= S.GENDER

            ,T.MagrID= S.MagrID

            ,T.DEPTNO= S.DEPTNO

            ,T.IsACTIVE=S.IsACTIVE

WHEN NOT MATCHED THEN

INSERT

VALUES(S.EMPID, S.EMPNAME, S.SAL, S.DOJ, S.LOC, S.GENDER, S.MagrID, S.DEPTNO, S.IsACTIVE)

;

-- TEST IT OUT

SELECT * FROM EMP_SRC

SELECT * FROM EMP_TGT