MS SQL MERGE 사용하기

http://technet.microsoft.com/ko-kr/library/bb522522(v=sql.105).aspx

 

 

TEST1 테이블에는 데이터가 없다 . MERGE 를 통해서 데이터 삽입

 

    MERGE TEST1  AS T

      USING  TEST2 AS S
      ON S.PostID = T.PostID

        WHEN MATCHED THEN

        UPDATE SET [NO] = S.[NO]

        WHEN NOT MATCHED THEN

        INSERT VALUES(S.[NO] , S.NAME)

        WHEN NOT MATCHED BY SOURCE THEN 
        DELETE;

    SELECT  * FROM TEST1    
    SELECT  * FROM TEST2

 

또는

    MERGE TEST1  AS T

      USING  ( SELECT 100 AS [NO] , 'KOJAEDOO' AS NAME , 0 AS POSTID) AS S
      ON S.PostID = T.PostID

        WHEN MATCHED THEN

        UPDATE SET [NO] = S.[NO]

        WHEN NOT MATCHED THEN

        INSERT VALUES(S.[NO] , S.NAME)

        WHEN NOT MATCHED BY SOURCE THEN 
        DELETE;

    SELECT  * FROM TEST1    
    SELECT  * FROM TEST2