The following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES table value constructor as the source table.

Suppose we create a table like this:

CREATE TABLE Idiots (
    IdiotId int IDENTITY(1,1) NOT NULL,
    FirstName varchar(50),
    LastName varchar(50)
    );

INSERT INTO Idiots VALUES 
    ('Peter', 'Griffin'), 
    ('Homer', 'Simpson'), 
    ('Ned', 'Flanders');

SELECT * FROM Idiots;

Result:

+-----------+-------------+------------+
| IdiotId   | FirstName   | LastName   |
|-----------+-------------+------------|
| 1         | Peter       | Griffin    |
| 2         | Homer       | Simpson    |
| 3         | Ned         | Flanders   |
+-----------+-------------+------------+

And suppose we want to perform a merge operation. We want to insert new rows, but if a row already exists with values that match those in our source, then we want to update the existing row instead (instead of inserting a new row).

We can do that like this:

DECLARE @Changes TABLE(Change VARCHAR(20));

MERGE INTO Idiots AS Target  
USING ( VALUES 
            (3, 'Ned', 'Okily Dokily!'), 
            (4, 'Lloyd','Christmas'), 
            (5, 'Harry', 'Dunne')
        ) AS Source ( IdiotId, FirstName, LastName )  
ON Target.IdiotId = Source.IdiotId
AND Target.FirstName = Source.FirstName
WHEN MATCHED THEN
    UPDATE SET FirstName = Source.FirstName, LastName = Source.LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (FirstName, LastName) VALUES (Source.FirstName, Source.LastName)
OUTPUT $action INTO @Changes;

SELECT Change, COUNT(*) AS Count  
FROM @Changes  
GROUP BY Change;

Result:

+----------+---------+
| Change   | Count   |
|----------+---------|
| INSERT   | 2       |
| UPDATE   | 1       |
+----------+---------+

Two new rows were inserted and one was updated.

Here, we used a table value constructor (VALUES) to specify the new values for the table.

In this case we checked the IdiotId and FirstName columns. Basically, if the ID and first name are the same, then we want to update the existing row instead of inserting a new one.

We used WHEN MATCHED to specify what should happen when data from our VALUES clause matches data in the target table. In this case, we simply update the row with the new data from VALUES.

We used WHEN NOT MATCHED BY TARGET to specify what should happen when there’s no match. In this case we insert a new row with the new data.

We also used a variable called @Changes to view the output of our merge operation. We used the OUTPUT clause after the merge operation for this. That’s what gave us the resulting table above.

We can now check the target table to see the impact that the merge operation had on it:

SELECT * FROM Idiots;

Result:

+-----------+-------------+---------------+
| IdiotId   | FirstName   | LastName      |
|-----------+-------------+---------------|
| 1         | Peter       | Griffin       |
| 2         | Homer       | Simpson       |
| 3         | Ned         | Okily Dokily! |
| 4         | Lloyd       | Christmas     |
| 5         | Harry       | Dunne         |
+-----------+-------------+---------------+

We can see that Idiot number 3 (Ned) has had his last name updated. We can also see that two new idiots have been added to the table (Lloyd and Harry).