[T-SQL]MERGE 練習

DECLARE @LOG TABLE(
    A NVARCHAR(5)
)
DECLARE @target TABLE (
    ID NVARCHAR(5),
    USERNAME NVARCHAR(50),
    EMAIL NVARCHAR(50),
    DEP NVARCHAR(50)

)
DECLARE @source TABLE (
    ID1 NVARCHAR(5),
    USERNAME1 NVARCHAR(50),
    EMAIL1 NVARCHAR(50),
    DEP1 NVARCHAR(50),
    DEP2 NVARCHAR(50)
)
INSERT INTO @target(ID,USERNAME,EMAIL,DEP)VALUES('1','Hank','aaa@mail','A1')
INSERT INTO @target(ID,USERNAME,EMAIL,DEP)VALUES('2','Hank456','bbb@mail','B1')
INSERT INTO @source(ID1,USERNAME1,EMAIL1,DEP1)VALUES('1','Hank123','ccc@mail','')
INSERT INTO @source(ID1,USERNAME1,EMAIL1,DEP1)VALUES('3','Hank666','ddd@mail','')

MERGE INTO @target as target –要被insert/update/delete的表
USING (SELECT ID1,EMAIL1,USERNAME1 ,DEP1 FROM @source ) as source –被參考的表
    ON (target.ID = source.ID1) –決定MATCHED與否
WHEN NOT MATCHED
    THEN
        INSERT (ID,USERNAME,EMAIL,DEP)
        VALUES (
             source.ID1
            ,source.USERNAME1
            ,source.EMAIL1
            ,'E'
            )
WHEN MATCHED
    THEN
        UPDATE 
        SET target.USERNAME = source.USERNAME1
            ,target.EMAIL = source.EMAIL1
            ,target.DEP = source.DEP1;

        
—結尾一定是分號

SELECT * FROM @target
–SELECT * FROM @source