Click here to monitor SSC

Generate a merge statement from table structure

Published 12 November 2013 5:00 pm

/*

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

2 Responses to “Generate a merge statement from table structure”

  1. Nigel Rivett says:

    Lets try the code again

    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

  2. Nigel Rivett says:

    And the result

    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
    );

Leave a Reply