syntaxhelp.com Your online cheat sheet!

Using SQL Server MERGE command

-- Creating the Target Table
DECLARE @tgt TABLE (OrdID INT, ItemID INT, Qty INT, Price MONEY);
INSERT INTO @tgt (OrdID, ItemID, Qty, Price)
	SELECT 1, 100, 10, 10.00 UNION ALL
	SELECT 1, 101, 10, 12.00
/*
OrdID       ItemID      Qty         Price
----------- ----------- ----------- ---------------------
1           100         10          10.00
1           101         10          12.00
*/

-- Creating the Source Table	
DECLARE @src TABLE (OrdID INT, ItemID INT, Qty INT, Price MONEY);
INSERT INTO @src (OrdID, ItemID, Qty, Price)
	SELECT 1, 100, 12, 10.00 UNION ALL
	SELECT 1, 102, 10, 12.00 UNION ALL
	SELECT 1, 103, 5, 7.00
/*
OrdID       ItemID      Qty         Price
----------- ----------- ----------- ---------------------
1           100         12          10.00
1           102         10          12.00
1           103         5           7.00
*/

-- Executing the Merge Statement to merge data from source to target
MERGE @tgt AS t
USING @src AS s
ON t.OrdID = s.OrdID AND t.ItemID = s.ItemID
WHEN MATCHED THEN
  UPDATE SET 
	t.Qty = s.Qty,
	t.Price = s.Price
WHEN NOT MATCHED THEN
  INSERT (OrdID, ItemID, Qty, Price) 
  VALUES (s.OrdID, s.ItemID, s.Qty, s.Price)
WHEN NOT MATCHED BY SOURCE THEN
	DELETE;
	
/*
Content of the target table after the MERGE operation:
OrdID       ItemID      Qty         Price
----------- ----------- ----------- ---------------------
1           100         12          10.00
1           102         10          12.00
1           103         5           7.00
*/