\/*<\/p>\n
This code generates a merge statement joining on the natural key<\/p>\n
and checking all other columns to see if they have changed.<\/p>\n
The full version deals with type 2 processing and an audit trail but this version is useful.<\/p>\n
Just the insert or update part is handy too.<\/p>\n
\n
\n
Change the table at the top (spt_values in master in the version) and the join columns for the merge in @nk.<\/p>\n
The output generated is at the top and the code to run to generate it below.<\/p>\n
*\/<\/p>\n
— Output<\/p>\n
merge spt_values a<\/p>\n
using spt_values b<\/p>\n
on a.name = b.name<\/p>\n
and a.number = b.number<\/p>\n
and a.type = b.type<\/p>\n
when matched and (1=0<\/p>\n
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)<\/p>\n
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)<\/p>\n
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)<\/p>\n
)<\/p>\n
then update set<\/p>\n
low = b.low<\/p>\n
, high = b.high<\/p>\n
, status = b.status<\/p>\n
when not matched by target then insert<\/p>\n
(<\/p>\n
name<\/p>\n
, number<\/p>\n
, type<\/p>\n
, low<\/p>\n
, high<\/p>\n
, status<\/p>\n
)<\/p>\n
values<\/p>\n
(<\/p>\n
b.name<\/p>\n
, b.number<\/p>\n
, b.type<\/p>\n
, b.low<\/p>\n
, b.high<\/p>\n
, b.status<\/p>\n
);<\/p>\n
\n
— Generator<\/p>\n
\n
set nocount on<\/p>\n
declare @t varchar(128) = ‘spt_values’<\/p>\n
\n
declare @i int = 0<\/p>\n
— this is the natural key on the table used for the merge statement join<\/p>\n
declare @nk table (ColName varchar(128))<\/p>\n
insert @nk select ‘Number’<\/p>\n
insert @nk select ‘Name’<\/p>\n
insert @nk select ‘Type’<\/p>\n
\n
declare @cols table (seq int, nkseq int, type int, colname varchar(128))<\/p>\n
;with cte as<\/p>\n
(<\/p>\n
select ordinal_position,<\/p>\n
type = case when columnproperty(object_id(@t), COLUMN_NAME,’IsIdentity’) = 1 then 3<\/p>\n
when nk.ColName is not null then 1 else 0 end,<\/p>\n
COLUMN_NAME<\/p>\n
from information_schema.columns c<\/p>\n
left join @nk nk<\/p>\n
on c.column_name = nk.ColName<\/p>\n
where table_name = @t<\/p>\n
)<\/p>\n
insert @cols(seq, nkseq, type, colname)<\/p>\n
select ordinal_position, row_number() over (partition by type order by ordinal_position) ,<\/p>\n
type, COLUMN_NAME<\/p>\n
from cte<\/p>\n
\n
Declare @result table (i int, j int, k int, data varchar(500))<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘merge ‘ + @t + ‘ a’<\/p>\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ using cte b’<\/p>\n
select @i = @i + 1<\/p>\n
insert @result(i, j, data)<\/p>\n
select @i, nkseq, ‘ ‘ + case when nkseq = 1 then ‘on’ else ‘and’ end + ‘ a.’ + ColName + ‘ = b.’ + ColName<\/p>\n
from @cols<\/p>\n
where type = 1<\/p>\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ when matched and (1=0’<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, j, k, data)<\/p>\n
select @i, seq, 1,<\/p>\n
‘ or (a.’ + ColName + ‘ b.’ + ColName + ‘)’<\/p>\n
+ ‘ or (a.’ + ColName + ‘ is null and b.’ + ColName + ‘ is not null)’<\/p>\n
+ ‘ or (a.’ + ColName + ‘ is not null and b.’ + ColName + ‘ is null)’<\/p>\n
from @cols<\/p>\n
where type = 1<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ )’<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ then update set’<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, j, data)<\/p>\n
select @i, nkseq,<\/p>\n
‘ ‘ + case when nkseq = 1 then ‘ ‘ else ‘, ‘ end<\/p>\n
+ colname + ‘ = b.’ + colname<\/p>\n
from @cols<\/p>\n
where type = 0<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ when not matched by target then insert’<\/p>\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ (‘<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, j, data)<\/p>\n
select @i, seq, ‘ ‘ + case when seq = 1 then ‘ ‘ else ‘, ‘ end + colname<\/p>\n
from @cols<\/p>\n
where type = 3<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ )’<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ values’<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ (‘<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, j, data)<\/p>\n
select @i, seq, ‘ ‘ + case when seq = 1 then ‘ ‘ else ‘, ‘ end + ‘b.’ + colname<\/p>\n
from @cols<\/p>\n
where type = 3<\/p>\n
\n
select @i = @i + 1<\/p>\n
insert @result(i, data)<\/p>\n
select @i, ‘ );’<\/p>\n
\n
select data from @result order by i,j,k,data<\/p>\n","protected":false},"excerpt":{"rendered":"
\/* 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……<\/p>\n","protected":false},"author":143519,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-6296","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143519"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=6296"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6296\/revisions"}],"predecessor-version":[{"id":42336,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6296\/revisions\/42336"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=6296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=6296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=6296"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=6296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}