12 November 2013

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

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2806 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

View all articles by Pop Rivett