USE Northwind
GO
-- Create some test data
SELECT * INTO EMP1 FROM Employees
SELECT * INTO EMP2 FROM Employees
GO
-- Take a Look
SELECT * FROM EMP1
GO
-- Create some changes
DELETE FROM EMP1 WHERE EmployeeID = 1
INSERT INTO EMP1 (LastName, FirstName) SELECT 'Kaiser','Brett'
UPDATE EMP1 SET LastName = 'White' WHERE EmployeeId = 4
GO

-- Find New records

SELECT l.* FROM EMP1 l LEFT JOIN EMP2 r ON l.EmployeeId = r.EmployeeId WHERE r.EmployeeID Is NULL

-- Find deleted records

SELECT r.* FROM EMP1 l RIGHT JOIN EMP2 r ON l.EmployeeId = r.EmployeeId WHERE l.EmployeeID Is NULL

-- Find Updated Records

SELECT  * FROM EMP1 l INNER JOIN EMP2 r ON l.EmployeeId = r.EmployeeId
 WHERE ( l.LastName <> r.LastName
   OR   l.FirstName <> r.FirstName)

GO

-- Add New reocrds
INSERT INTO EMP2 (LastName, FirstName) 
SELECT l.LastName, l.FirstName FROM EMP1 l LEFT JOIN EMP2 r ON l.EmployeeId = r.EmployeeId WHERE r.EmployeeID Is NULL
-- Delete missing
DELETE FROM EMP2
WHERE EmployeeID IN (
SELECT r.EmployeeID  FROM EMP1 l RIGHT JOIN EMP2 r ON l.EmployeeId = r.EmployeeId WHERE l.EmployeeID Is NULL)

-- Update the changes ....update all columns for simplicity

UPDATE r
SET r.LastName = l.Lastname
  , r.FirstName = l.Firstname
FROM EMP1 l INNER JOIN EMP2 r ON l.EmployeeId = r.EmployeeId
 WHERE ( l.LastName <> r.LastName
   OR   l.FirstName <> r.FirstName)
GO
-- Take a peek

SELECT * FROM EMP1
UNION ALL
SELECT * FROM EMP2
ORDER BY EmployeeID
GO
 
-- Clean up this mess

DROP TABLE EMP1
DROP TABLE EMP2
GO