--Create a target table

CREATE TABLE Products

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

) 

GO

--Insert records into target table

INSERT INTO Products

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 20.00),

(3, 'Muffin', 30.00),

(4, 'Biscuit', 40.00)

GO

--Create source table

CREATE TABLE UpdatedProducts

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

) 

GO

--Insert records into source table

INSERT INTO UpdatedProducts

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 25.00),

(3, 'Muffin', 35.00),

(5, 'Pizza', 60.00)

GO

SELECT * FROM Products

SELECT * FROM UpdatedProducts

GO

MERGE Products AS TARGET

USING UpdatedProducts AS SOURCE 

ON (TARGET.ProductID = SOURCE.ProductID) 

WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 

OR TARGET.Rate <> SOURCE.Rate THEN 

UPDATE SET TARGET.ProductName = SOURCE.ProductName, 

TARGET.Rate = SOURCE.Rate

WHEN NOT MATCHED BY TARGET THEN 

INSERT (ProductID, ProductName, Rate) 

VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

WHEN NOT MATCHED BY SOURCE THEN 

DELETE;

----------------------------------------------------------------------------------------------------------------------

Merge 테이블 사용할 기회가 생겨 구글링 해보았음.

좋은 예제인거 같아서 올려봄.

[참고] http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

[참고] http://msdn.microsoft.com/en-us/library/bb510625.aspx