Lesson 40. Loading Tables With MERGE

The MERGE clause is the most powerful SQL clause known to man. With MERGE, you can take data from one table and load it into another table. Unlike INSERT INTO SELECT, MERGE gives you way more power in how you go about doing that.

With great power comes great complexity and a full explanation of SQL MERGE is FAR outside the scope of this tutorial.

The most common use case I have for MERGE is loading Type II slowly changing dimensions in data warehouses. I talk about this extensively in the section titled Loading A Type II Slowly Changing Dimension With SQL Merge.

The process of using MERGE works like this.

1. Identify the table you will load data into.

2. Identify the table that you will use as the source of your data.

3. Identify how records in those two tables are connected.

4. Give instructions on what to do when records do not match.

5. Give instructions on what to do when records do match.

Examples

A Basic MERGE Example

In [ ]:

USE demo

DROP TABLE IF EXISTS Person
DROP TABLE IF EXISTS PersonStageTable

CREATE TABLE Person(
PersonID BIGINT NOT NULL,
FirstName NVARCHAR(50) NULL,
LastName NVARCHAR(50) NULL,
SourceSystemKey NVARCHAR(50) NULL,
)

CREATE TABLE PersonStageTable(
PersonID BIGINT NOT NULL,
FirstName NVARCHAR(50) NULL,
LastName NVARCHAR(50) NULL,
SourceSystemKey NVARCHAR(50) NULL,
)

INSERT INTO Person(PersonID, FirstName, LastName, SourceSystemKey)
SELECT 1, 'Bob', 'Wakefield',1

INSERT INTO PersonStageTable(PersonID, FirstName, LastName, SourceSystemKey)
SELECT 1,'Bob','Johnson',1
UNION 
SELECT 2,'Sally','Ride',2

SELECT * FROM Person

SELECT * FROM PersonStageTable


--*****Merge example beings here.*****

MERGE Person AS target
USING (
SELECT
PersonID,
FirstName,
LastName,
SourceSystemKey
FROM PersonStageTable
) AS source
ON (target.SourceSystemKey = source.SourceSystemKey)

WHEN NOT MATCHED THEN
INSERT (
PersonID,
FirstName,
LastName,
SourceSystemKey
)
VALUES (
PersonID,
FirstName,
LastName,
SourceSystemKey
)

WHEN MATCHED THEN
UPDATE
SET
target.PersonID = source.PersonID,
target.FirstName = source.FirstName,
target.LastName = source.LastName,
target.SourceSystemKey = source.SourceSystemKey
;

SELECT * FROM Person

DROP TABLE Person
DROP TABLE PersonStageTable