Hi @Mitch McConnell ,
You can use a MERGE
statement for your scenario.
Here is a conceptual example for you.
SQL
-- DDL and sample data population, start
--Create a target table
DECLARE @Products TABLE (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
);
INSERT INTO @Products
VALUES (1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00);
--Create source table
DECLARE @UpdatedProducts TABLE (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
);
INSERT INTO @UpdatedProducts
VALUES (1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00);
-- DDL and sample data population, end
--SELECT * FROM @Products;
--SELECT * FROM @UpdatedProducts;
--MERGE SQL statement
--Synchronize the target table with
--refreshed data from source table
;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET
USING @UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
-- When rows are matched, update the rows if there is any change
-- but only if something needs to be updated!!!
WHEN MATCHED AND (TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate) THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no rows are matched, insert the incoming rows from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same row does not exist in source table
--then delete this row from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
-- INTO #log (a, dt, what);
/*
We can see the results:
- Coffee rate was updated from 20.00 to 25.00,
- Muffin rate was updated from 30.00 to 35.00,
- Biscuit was deleted
- Pizza was inserted.
*/
SELECT * FROM @Products;