<charliedigital /> Programming, Politics, and uhh…pineapples

--// Create our SOURCE table
DECLARE @t1 TABLE (
    id      int,
    name    nvarchar(16),
    email   nvarchar(32)
)

--// Create our TARGET table
DECLARE @t2 TABLE (
    id      int,
    name    nvarchar(16),
    email   nvarchar(32)
)

--// Insert values into SOURCE table
INSERT INTO @t1 VALUES (1, 'Charles Chen', 'charles.chen@domain.com')
INSERT INTO @t1 VALUES (2, 'Sandra Chen', 'sandra.chen@domain.com')
INSERT INTO @t1 VALUES (3, 'Brad Wright', 'brad.wright@domain.com')

--// Insert values into TARGET table
INSERT INTO @t2 VALUES (4, 'Brady Sines', 'brady.sines@domain.com')
INSERT INTO @t2 VALUES (1, '', '')

--// Select values from the TARGET table (just to verify)
SELECT * FROM @t2

--// Merge records from source to target
MERGE
    @t2 AS [target]
USING
    @t1 AS [source]
ON
    ([target].id = [source].id)
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT(id, name, email)
        VALUES([source].id, [source].name, [source].email)
WHEN MATCHED
    THEN
        UPDATE SET
            [target].name = [source].name,
            [target].email = [source].email
--// Get the journal of actions
OUTPUT $action, inserted.*, deleted.*;

--// Grab the resultant table
SELECT * FROM @t2