SQL Server Merge Update With Partial Sources

Question

I have a target table for which partial data arrives at different times from 2 departments. The keys they use are the same, but the fields they provide are different. Most of the rows they provide have common keys, but there are some rows that are unique to each department. My question is about the fields, not the rows:

Scenario

• the target table has a key and 30 fields.
• Dept. 1 provides fields 1-20
• Dept. 2 provides fields 21-30

Suppose I loaded Q1 data from Dept. 1, and that created new rows 100-199 and populated fields 1-20. Later, I receive Q1 data from Dept. 2. Can I execute the same merge code I previously used for Dept. 1 to update rows 100-199 and populate fields 21-30 without unintentionally changing fields 1-20? Alternatively, would I have to tailor separate merge code for each Dept.?

In other words, does (or can) "Merge / Update" operate only on target fields that are present in the source table while ignoring target fields that are NOT present in the source table? In this way, Dept. 1 fields would NOT be modified when merging Dept. 2, or vice-versa, in the event I get subsequent corrections to this data from either Dept.

Answer 1

You can use a merge instruction, where you define a source and a target data, and what happens when a registry is found on both, just on the source, just on the target, and even expand it with custom logic, like it's just on the source, and it's older than X, or it's from department Y.

-- I'm skipping the fields 2-20 and 22-30, just to make this shorter.
create table #target (
    id int primary key,
    field1 varchar(100),  -- and so on until 20
    field21 varchar(100), -- and so on until 30
)
create table #dept1 (
    id int primary key,
    field1 varchar(100)
)
create table #dept2 (
    id int primary key,
    field21 varchar(100)
)

/*
Creates some data to merge into the target.
The expected result is:
| id | field1   | field21  |
| -  | -        | -        |
| 1  | dept1: 1 | dept2: 1 |
| 2  |          | dept2: 2 |
| 3  | dept1: 3 |          |
| 4  | dept1: 4 | dept2: 4 |
| 5  |          | dept2: 5 |
*/
insert into #dept1 values
(1,'dept1: 1'),
--(2,'dept1: 2'),
(3,'dept1: 3'),
(4,'dept1: 4')
insert into #dept2 values
(1,'dept2: 1'),
(2,'dept2: 2'),
--(3,'dept2: 3'),
(4,'dept2: 4'),
(5,'dept2: 5')

-- Inserts the data from the first department. This could be also a merge, it necessary.
insert into #target(id, field1)
    select id, field1 from #dept1

merge into #target t
using (select id, field21 from #dept2) as source_data(id, field21)
on (source_data.id = t.id)
when matched then update set field21=source_data.field21
when not matched by source and t.field21 is not null then delete -- you can even use merge to remove some records that match your criteria
when not matched by target then insert (id, field21) values (source_data.id, source_data.field21); -- Every merge statement should end with ;

select * from #target

You can see this code running on this DB Fiddle