Generate a merge statement from table structure
- by Nigel Rivett
This code generates a merge statement joining on he natural key
and checking all other columns to see if they have changed.
The full version deals with type 2 processing and an audit trail but this version is useful.
Just the insert or update part is handy too.
Change the table at the top (spt_values in master in the version) and the join columns for the merge in @nk.
The output generated is at the top and the code to run to generate it below.
Output
merge spt_values a
using spt_values b
on a.name = b.name
and a.number = b.number
and a.type = b.type
when matched and (1=0
or (a.low b.low) or (a.low is null and b.low is not null) or (a.low is not null and b.low is null)
or (a.high b.high) or (a.high is null and b.high is not null) or (a.high is not null and b.high is null)
or (a.status b.status) or (a.status is null and b.status is not null) or (a.status is not null and b.status is null)
)
then update set
low = b.low
, high = b.high
, status = b.status
when not matched by target then insert
(
name
, number
, type
, low
, high
, status
)
values
(
b.name
, b.number
, b.type
, b.low
, b.high
, b.status
);
Generator
set nocount on
declare @t varchar(128) = 'spt_values'
declare @i int = 0
-- this is the natural key on the table used for the merge statement join
declare @nk table (ColName varchar(128))
insert @nk select 'Number'
insert @nk select 'Name'
insert @nk select 'Type'
declare @cols table (seq int, nkseq int, type int, colname varchar(128))
;with cte as
(
select ordinal_position,
type = case when columnproperty(object_id(@t), COLUMN_NAME,'IsIdentity') = 1 then 3
when nk.ColName is not null then 1 else 0 end,
COLUMN_NAME
from information_schema.columns c
left join @nk nk
on c.column_name = nk.ColName
where table_name = @t
)
insert @cols (seq, nkseq, type, colname)
select ordinal_position, row_number() over (partition by type order by ordinal_position) ,
type, COLUMN_NAME
from cte
declare @result table (i int, j int, k int, data varchar(500))
select @i = @i + 1
insert @result (i, data)
select @i, 'merge ' + @t + ' a'
select @i = @i + 1
insert @result (i, data)
select @i, ' using cte b'
select @i = @i + 1
insert @result (i, j, data)
select @i, nkseq, ' ' + case when nkseq = 1 then 'on' else 'and' end + ' a.' + ColName + ' = b.' + ColName
from @cols
where type = 1
select @i = @i + 1
insert @result (i, data)
select @i, ' when matched and (1=0'
select @i = @i + 1
insert @result (i, j, k, data)
select @i, seq, 1,
' or (a.' + ColName + ' b.' + ColName + ')'
+ ' or (a.' + ColName + ' is null and b.' + ColName + ' is not null)'
+ ' or (a.' + ColName + ' is not null and b.' + ColName + ' is null)'
from @cols
where type 1
select @i = @i + 1
insert @result (i, data)
select @i, ' )'
select @i = @i + 1
insert @result (i, data)
select @i, ' then update set'
select @i = @i + 1
insert @result (i, j, data)
select @i, nkseq,
' ' + case when nkseq = 1 then ' ' else ', ' end
+ colname + ' = b.' + colname
from @cols
where type = 0
select @i = @i + 1
insert @result (i, data)
select @i, ' when not matched by target then insert'
select @i = @i + 1
insert @result (i, data)
select @i, ' ('
select @i = @i + 1
insert @result (i, j, data)
select @i, seq, ' ' + case when seq = 1 then ' ' else ', ' end + colname
from @cols
where type 3
select @i = @i + 1
insert @result (i, data)
select @i, ' )'
select @i = @i + 1
insert @result (i, data)
select @i, ' values'
select @i = @i + 1
insert @result (i, data)
select @i, ' ('
select @i = @i + 1
insert @result (i, j, data)
select @i, seq, ' ' + case when seq = 1 then ' ' else ', ' end + 'b.' + colname
from @cols
where type 3
select @i = @i + 1
insert @result (i, data)
select @i, ' );'
select data from @result order by i,j,k,data
