SQL Server data merge builder script.
-- use master/or some_data_base_here;
-- go
if object_id('dbo.sp_DataMergeBuilder', 'P') is null
begin
exec sys.sp_executesql N'create proc dbo.sp_DataMergeBuilder as return 0;'
end;
go
alter procedure dbo.sp_DataMergeBuilder
@table sysname,
@includeData bit = 0,
@whereClause nvarchar(1000) = ''
as
/*--------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
© 2000-15 - NightOwl Development - All rights Reserved
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Purpose : Generates bolier plate merge statement for use in creating a generic merge statement.
Returns : merge script for the specified table
Notes : this is a work in progress
History :
Date Developer Work Item Modification
------------------------------ ------------------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2011-12-30 P. Hunter 0 Object created.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------*/
set nocount on;
declare @baseName sysname,
@cmd nvarchar(max) = '',
@crlf char(2) = char(13) + char(10),
@data nvarchar(max) = '',
@dbName sysname = db_name(),
@errMsg nvarchar(1000),
@hasIdent bit = 0,
@maxColumn int,
@objectId int,
@propName sysname,
@rows int,
@tab char(1) = char(9),
@userName sysname = ''''+suser_sname()+'''',
@xml xml;
set @cmd = 'use '+@dbName+';
select @objectId = object_id
, @baseName = t.name
, @propName = schema_name(t.schema_id) + ''.'' + t.name
, @maxColumn = t.max_column_id_used
, @hasIdent = (select count(1) from sys.identity_columns ic where ic.object_id = t.object_id)
from sys.tables t
where t.object_id = object_id(@table);';
exec sys.sp_executesql
@cmd,
N'@objectId int out, @basename sysname out, @propName sysname out, @maxColumn int out, @table sysname, @hasIdent bit out',
@objectId out,
@baseName out,
@propName out,
@maxColumn out,
@table,
@hasIdent out;
if @objectId is null
begin
set @errMsg = 'The table name "'+isnull(@table, 'nothing provided')+'" could not be located in the '+@dbName+'database.';
raiserror(@errMsg, 15, 1);
end;
create table #excludeColumns(
name sysname primary key,
defaultValue sysname
);
insert into #excludeColumns(
name,
defaultValue
)
values
(
'CreatedBy', @userName
),
(
'CreatedDate', 'getdate()'
),
(
'CreatedOn', 'getdate()'
),
(
'ModifiedBy', @userName
),
(
'ModifiedDate', 'getdate()'
),
(
'ModifiedOn', 'getdate()'
),
(
'UpdatedBy', @userName
),
(
'UpdatedDate', 'getdate()'
),
(
'UpdatedOn', 'getdate()'
);
create table #columns(
column_id smallint primary key,
name sysname,
isIdentity bit,
isNullable bit,
isExcluded bit,
tabs tinyint,
remdr tinyint,
minId smallint,
maxId smallint,
lastMerge smallint,
maxTabs tinyint,
typeName sysname,
definition nvarchar(max)
);
set @cmd = 'use '+@dbName+';
insert #columns
select s.column_id
, s.name
, s.is_identity
, s.is_nullable
, isExcluded = case s.name when x.name then 1 else 0 end
, tabs = (a.maxTabs - ((len(s.name)) / 4))
, remdr = (len(s.name) % 4)
, a.minId
, a.maxId
, a.lastMerge
, a.maxTabs
, typeName = t.name
, definition = case left(d.definition, 2)
when ''(('' then replace(replace(d.definition, ''(('', ''''), ''))'', '''')
else replace(substring(definition, 2, len(d.definition) - 2), ''(1)'', ''1'')
end
from sys.columns s
join sys.types t
on s.user_type_id = t.user_type_id
and s.system_type_id = t.system_type_id
and t.name not like ''%binary''
left
join #excludeColumns x
on x.name = s.name
left
join sys.default_constraints d
on d.parent_object_id = s.object_id
and d.parent_column_id = s.column_id
outer
apply( select minId = min(c.column_id)
, maxId = max(c.column_id)
, lastMerge = max(case c.name when ec.name then 0 else c.column_id end)
, maxTabs = max(len(c.name) + 4) / 4
from sys.columns c
left
join #excludeColumns ec
on ec.name = c.name
where c.object_id = s.object_id
and c.is_computed = 0
) a
where s.object_id = @objectId
and s.is_computed = 0';
exec sys.sp_executesql
@cmd,
N'@objectId int',
@objectId;
-- build the join criteria to prefer a "natural" unique key over a surogate key (identity) where available
create table #join(
script nvarchar(max),
rowId int identity
primary key
);
set @cmd = 'use '+@dbName+';
insert #join ( script )
select script = case i.key_ordinal
when 1 then '' on tgt.''
else '' and tgt.''
end + c.name + replicate(@tab, c.tabs) + ''= src.'' + c.name
from #columns c
join( select joinOn = rank() over (order by i.index_id)
, ic.*
from sys.index_columns ic
join sys.indexes i
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and i.is_unique = 1
where ic.object_id = @objectId
) i on i.column_id = c.column_id
and i.joinOn = 1;';
exec sys.sp_executesql
@cmd,
N'@objectId int, @tab char(1)',
@objectId,
@tab;
-- they want to include data, this checks to see if there is data to extract...
if @includeData = 1
begin
set @cmd = 'select top 1 @includeData = count(1) from '+@dbName+'.'+@propName;
exec sys.sp_executesql
@cmd,
N'@includeData bit out',
@includeData out;
end;
create table #cteData(
script nvarchar(max),
id int identity
primary key
);
/*---------------------------------------------------------------------------------
** extract the data for the table - OR - create a stub of the columns to be used...
---------------------------------------------------------------------------------*/
-- first, define the cte with column names
insert into #cteData(
script
)
select
case c.column_id
when m.minId
then 'with cte_'+@baseName+@crlf+@tab+'('+@tab+c.name
else @tab+','+@tab+c.name+case c.column_id
when m.maxId
then @crlf+@tab+')'+@crlf+' as'+@crlf+'('
else ''
end
end
from #columns as c
cross apply (select
minId = min(c.column_id),
maxId = max(c.column_id)
from #columns as c
where c.name not in (select
name
from #excludeColumns) ) as m left join #excludeColumns as x
on x.name = c.name
where x.name is null
order by
c.column_id;
if @includeData = 1
begin
-- now extract the data for the table...
set @cmd = '';
-- build a dynamic query that builds the extract command
select
@cmd = @cmd+case c.column_id
when 1
then @crlf+'select script = '' select '' + replace(replace('
else ' + '', '' + '
end+'isnull('+case
when c.typeName like '%char'
then '''#|#'' + '+c.name+' + ''#|#'''
when c.typeName like '%date%'
or c.typeName like '%time%'
then '''#|#'' + convert(varchar(50), '+c.name+', 121) + ''#|#'''
when c.typeName like '%int'
or c.typeName like 'bit'
or c.typeName like 'dec%'
or c.typeName like 'num%'
or c.typeName like 'flo%'
or c.typeName like 'rea%'
then 'convert(varchar(50), '+c.name+')'
else c.name
end+', ''null'')'
from #columns as c
cross apply (select
minId = min(c.column_id),
maxId = max(c.column_id)
from #columns as c
where c.name not in (select
name
from #excludeColumns) ) as m left join #excludeColumns as x
on x.name = c.name
where x.name is null
order by
c.column_id;
set @cmd = @cmd+', '''''''' ,'''''''''''' ), ''#|#'','''''''') + '''+@crlf+@tab+@tab+'union all''';
set @cmd = 'use '+@dbName+';'+@crlf+'insert #cteData ( script )'+@cmd+@crlf+'from '+@propName+@crlf+case
when @whereClause > ''
then @whereClause + +@crlf
else ''
end+'order by '+ (select top 1
name
from #columns
order by
isIdentity desc,
column_id) +';';
-- execute the resulting query
exec sys.sp_executesql
@cmd;
set @rows = @@rowcount;
update #cteData
set
script = replace(script, @tab+@tab+'union all', ')')
where
Id = (select
max(id)
from #cteData);
end;
if @includeData = 0 -- build a stub select statement or...
or @rows = 0 -- no rows generated from the data
begin
-- either there's no data to include or the data isn't suppoed to be included
set @includeData = 0;
-- create a stub for each column
insert into #cteData(
script
)
select
script = case c.column_id
when m.minId
then ' select'
else ' ,'
end+@tab+c.name+replicate(@tab, c.tabs)+'= null'+case c.column_id
when m.maxId
then @crlf+')'
else ''
end
from #columns as c
cross apply (select
minId = min(c.column_id),
maxId = max(c.column_id)
from #columns as c
where c.name not in (select
name
from #excludeColumns) ) as m left join #excludeColumns as x
on x.name = c.name
where x.name is null
order by
c.column_id;
end;
/*------------------------------------
** begin building the output script...
------------------------------------*/
create table #output(
script nvarchar(max),
rowId int identity
primary key
);
if @hasIdent = 1
begin
insert into #output(
script
)
select
script = '-- This table has an identity column so preserve those number on insert'
union all
select
script = 'set identity_insert '+@propName+' on;'+@crlf+'go';
end;
-- setup the stub cte that will hold the data to be merged
insert into #output(
script
)
select
script = '-- create cte containing the source data to be merged into the target table...';
-- provide the cteData data or "fake data"
insert into #output(
script
)
select
script
from #cteData
order by
Id;
-- create top half of except clause to get the differences between the cte and target table
insert into #output(
script
)
select
script = '-- using an EXCEPT statement generates the true diferences between the cte source and the target table you want to merge into...';
-- create top half of except clause to get the differences between the cte and target table
insert into #output(
script
)
select
script = case c.column_id
when c.minId
then 'merge '+@propName+' tgt'+@crlf+'using( select'
else replicate(@tab, 3)+','
end+@tab+c.name+case c.column_id
when c.lastMerge
then @crlf+' from cte_'+@baseName+@crlf+case @includeData
when 0
then ' where '+ (select top 1
name
from #columns as c
order by
c.isIdentity desc,
c.column_id) +' is not null'+@crlf
else ''
end+' EXCEPT -- return only true differences'
else ''
end
from #columns as c
where c.isExcluded = 0;
-- create bottom half of except clause to get the differences between the cte and target table
insert into #output(
script
)
select
script = case c.column_id
when c.minId
then ' select'
else replicate(@tab, 3)+','
end+@tab+c.name+case c.column_id
when c.lastMerge
then @crlf+' from '+@propName+@crlf+' ) src'
else ''
end
from #columns as c
where c.isExcluded = 0;
-- add the join criteria
insert into #output(
script
)
select
script
from #join as j;
-- create the update portion
insert into #output(
script
)
select
script = case c.column_id
when b.minId
then 'when matched'+@crlf+'then update'+@crlf+' set '
else replicate(@tab, 3)+','
end+@tab+c.name+replicate(@tab, c.tabs)+'= '+case c.name
when x.name
then x.defaultValue
else 'src.'+c.name
end
from #columns as c
cross join (select
minId = min(column_id),
maxId = max(column_id)
from #columns as m
where m.name not like 'Created[BD][ya]%'
and m.isIdentity = 0) as b left join #excludeColumns as x
on x.name = c.name
where c.name not like 'Created[BD][ya]%'
and c.isIdentity = 0
order by
c.column_id;
-- create the top part of the insert
insert into #output(
script
)
select
script = case c.column_id
when c.minId
then 'when not matched'+@crlf+'then insert'+@crlf+' ('
else replicate(@tab, 3)+','
end+@tab+c.name+case c.column_id
when c.maxId
then @crlf+' )'
else ''
end
from #columns as c
left join #excludeColumns as x
on x.name = c.name
order by
c.column_id;
-- create the bottom/values part of the insert
insert into #output(
script
)
select
script = case c.column_id
when c.minId
then ' values'+@crlf+' ('
else replicate(@tab, 3)+','
end+@tab+case
when c.name = x.name
then x.defaultValue+' -- '+c.name
when c.definition > ''
then 'isnull(src.'+c.name+', '+c.definition+')'
else 'src.'+c.name
end+case c.column_id
when c.maxId
then @crlf+' );'
else ''
end
from #columns as c
left join #excludeColumns as x
on x.name = c.name
order by
c.column_id;
-- complete the script with a batch separator
insert into #output(
script
)
values
(
'go'
);
if @hasIdent = 1
begin
insert into #output(script)
select script = '-- reset the identity engine and reseed the identity value'
union all
select script = 'set identity_insert '+@propName+' off;'+@crlf+'go'
union all
select
script = 'dbcc checkident('''+@propName+''', reseed);'+@crlf+'go'+@crlf;
end;
-- return the results
select script
from #output as o
order by o.rowId;
go