In SQL Server 2008, we can do a table synchronisation in one transaction of SQL Command. For example normally, we have a source table and then we have an updated records and what we would like to do is "I want to delete the record from the source table if it is not in my updated list, if there is a matched record then I’d like to update the value to the latest value, and if the record is not exists at all on the source table then I’d like to insert it"

I got a sample query with the recordset and syntax below in how to use "MERGE" to handle the case above. I got the actual sample code from (http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/) – by Arshad Ali

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = ‘Products’)
BEGIN
DROP TABLE Products
END
GO
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
SELECT * FROM Products
GO
–Create source table
DECLARE @UpdatedProducts TABLE
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
–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)

SELECT * FROM @UpdatedProducts

MERGE Products AS TARGET
USING @UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
–When records are matched, update
–the records if there is any change
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 records are matched, insert
–the incoming records 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 record does not exist in source table
–then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT * FROM Products

GO