/*

This code generates a merge statement joining on the 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