Complete Guide to MERGE INTO in Databricks

Here’s a complete example of using MERGE INTO in Databricks SQL. This example demonstrates how to update a target table (employees_target) by comparing it with a lookup table (employees_lookup). The lookup table contains updated employee information.

1️⃣ Create Tables

Create the Target Table (employees_target)
CREATE TABLE employees_target (
    emp_id INT,
    name STRING,
    department STRING,
    salary DOUBLE
);

-- Create the Lookup Table (employees_lookup)
CREATE TABLE employees_lookup (
    emp_id INT,
    name STRING,
    department STRING,
    salary DOUBLE
);

2️⃣ Insert Sample Data

Insert data into employees_target (Old data)
INSERT INTO employees_target VALUES 
(1, 'Alice', 'HR', 60000),
(2, 'Bob', 'IT', 70000),
(3, 'Charlie', 'Finance', 80000),
(4, 'David', 'IT', 75000);

-- Insert data into employees_lookup (Updated data)
INSERT INTO employees_lookup VALUES 
(1, 'Alice', 'HR', 65000),   -- Updated salary
(2, 'Bob', 'IT', 70000),     -- No change
(3, 'Charlie', 'Finance', 85000), -- Updated salary
(5, 'Eve', 'Marketing', 90000);   -- New employee

Databricks top books – Buy now

3️⃣ Merge Operation (Update & Insert)

MERGE INTO employees_target AS target
USING employees_lookup AS source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary  -- Updating only salary
WHEN NOT MATCHED THEN
    INSERT (emp_id, name, department, salary)
    VALUES (source.emp_id, source.name, source.department, source.salary);

4️⃣ Final Data in employees_target

After running the MERGE INTO query, employees_target will have:

✅ Changes Applied:

1. Alice’s salary updated (60000 → 65000)
2. Charlie’s salary updated (80000 → 85000)
3. Eve (new employee) inserted from lookup table
4. David’s record remains unchanged