Generate a merge statement from table structure

/*

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

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue