----------------------------------------------------------------------------- Helpers --------------------------------------------------------------
IF OBJECT_ID('dbo.TblUser') IS NOT NULL
DROP TABLE TblUser;
IF OBJECT_ID('dbo.TblEmployee') IS NOT NULL
DROP TABLE TblEmployee;
DROP TABLE TblUser;
DROP TABLE TblEmployee;
DROP TABLE IF EXISTS TblUser;
DROP TABLE IF EXISTS TblEmployee;
TRUNCATE TABLE TblUser;
TRUNCATE TABLE TblEmployee;
SELECT * FROM TblUser;
SELECT * FROM TblEmployee;
----------------------------------------------------------------------------- Table and Data --------------------------------------------------------------
CREATE TABLE TblUser(
Id INT PRIMARY KEY,
Email NVARCHAR(100),
CreatedDateTime DATETIME NOT NULL,
UpdatedDateTime DATETIME NULL,
SyncCreatedDateTime DATETIME NOT NULL,
SyncUpdatedDateTime DATETIME NULL
);
CREATE TABLE TblEmployee(
Id INT PRIMARY KEY,
Email NVARCHAR(100),
CreatedDateTime DATETIME NOT NULL,
UpdatedDateTime DATETIME NULL
);
INSERT
INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
VALUES
(1001, 'Kong@hotmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be deleted
(1000, 'Han@hotmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be deleted
(1, 'Dan@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be as it is
(2, 'Ben@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be as it is
(3, 'Danx@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102), GETDATE()), --should be as it is
(4, 'Benx@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102), GETDATE()), --should be as it is
(5, 'Jhon@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be modified
(6, 'ken@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL , GETDATE()), --should be modified
(7, 'Aron@facebook.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102) , GETDATE()), --should be modified
(8, 'Kim@facebook.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102) , GETDATE()); --should be modified
--(9, 'Tom@yahoo.com', DATEADD(DD,1,GETDATE()), NULL , GETDATE()) --should be added
--(10, 'Jeff@yahoo.com',DATEADD(DD,1,GETDATE()), NULL , GETDATE()) --should be added
INSERT
INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
VALUES
(1, 'Dan-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(2, 'Ben-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(3, 'Danx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102)),
(4, 'Benx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102)),
(5, 'Jhon@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(6, 'ken@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(7, 'Aron@mail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-20', 102)),
(8, 'Kim@mail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-20', 102)),
(9, 'Tom@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL),
(10, 'Jeff@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL);
2 row deleted
4 row updated
2 row added
----------------------------------------------------------------------------- Using Regular Insert Update --------------------------------------------------------------
-------------------------------- delete rows
--SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
SELECT E.Id
FROM TblEmployee E
WHERE E.Id = TblUser.Id
);
-------------------------------- update rows
WITH ExistingUsers
AS
(
SELECT *
FROM TblEmployee E
WHERE EXISTS (
SELECT Id
FROM TblUser U
WHERE E.Id = U.Id
AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, GETDATE()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, GETDATE())
)
)
UPDATE U
SET
U.Email = E.Email,
U.UpdatedDateTime = E.UpdatedDateTime,
U.SyncUpdatedDateTime = GETDATE()
FROM TblUser U
JOIN ExistingUsers E ON U.Id = E.Id;
------------------------------ add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, GETDATE()
FROM TblEmployee E
WHERE NOT EXISTS (
SELECT Id
FROM TblUser U
WHERE E.Id = U.Id
);
----------------------------------------------------------------------------- Using Merge Query --------------------------------------------------------------
MERGE TblUser AS T
USING TblEmployee AS S
ON T.Id = S.Id
WHEN MATCHED AND COALESCE(S.UpdatedDateTime, S.CreatedDateTime, GETDATE()) <> COALESCE(T.UpdatedDateTime, T.CreatedDateTime, GETDATE())
THEN UPDATE
SET
T.Email = S.Email,
T.UpdatedDateTime = S.UpdatedDateTime,
T.SyncUpdatedDateTime = GETDATE()
WHEN NOT MATCHED BY TARGET
THEN INSERT (Id, Email, CreatedDateTime, SyncCreatedDateTime)
VALUES (S.Id, S.Email, S.CreatedDateTime, GETDATE())
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
MERGE TblUser AS T
USING ( VALUES
(1, 'Dan-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(2, 'Ben-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(3, 'Danx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102)),
(4, 'Benx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-16', 102)),
(5, 'Jhon@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(6, 'ken@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(7, 'Aron@mail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-20', 102)),
(8, 'Kim@mail.com', CONVERT(DATETIME, '2021-08-15', 102), CONVERT(DATETIME, '2021-08-20', 102)),
(9, 'Tom@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL),
(10, 'Jeff@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL)
) AS S(Id, Email, CreatedDateTime, UpdatedDateTime)
ON T.Id = S.Id
WHEN MATCHED AND COALESCE(S.UpdatedDateTime, S.CreatedDateTime, GETDATE()) <> COALESCE(T.UpdatedDateTime, T.CreatedDateTime, GETDATE())
THEN UPDATE
SET
T.Email = S.Email,
T.UpdatedDateTime = S.UpdatedDateTime,
T.SyncUpdatedDateTime = GETDATE()
WHEN NOT MATCHED BY TARGET
THEN INSERT (Id, Email, CreatedDateTime, SyncCreatedDateTime)
VALUES (S.Id, S.Email, S.CreatedDateTime, GETDATE())
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
