Donate to support site


home

-- Generate an insert, update and merge statement from the table structure.
Author Nigel Rivett
This generates an insert statement to insert all data from a staging table to the productions table.
There are column z_inserted, z_updated in the table which get set to getdate().
It is also useful to take the column list and the update list to produce your own statement.

Future enhancements
Generate update statement for existing data
Generate merge statement to insert, update, delete data
Generate merge statement to maintain trype 2 dimension
	Set end date on rows for updated, deleted data
	Add rows with start and end date for inserted, updated data

/*
if object_id('test1','U') is not null
	drop table test1
go
create table test1
	(
		  ID1 varchar(20) not null
		, ID2 varchar(20) not null
		, col1 varchar(20)
		, col02 varchar(20)
		, col003 int
		, col0004 datetime

		, z_inserted datetime not null
		, z_updated datetime not null
	)
go
*/

if object_id('s_Generate_Insert','P') is not null
	drop proc s_Generate_Insert
go
create proc s_Generate_Insert
@table varchar(200)
, @table_stg varchar(200)
, @Margin1Tabs int = 2		-- these set margins and tab length to format the data
, @Margin2Tabs int = 4
, @tablen int = 4
as
/*
exec s_Generate_Insert
		@table = 'test1'
		, @table_stg = 'test1_stg'
*/

-- Generate insert statement
declare @sql varchar(max)
declare @sql1 varchar(max)
declare @sql2 varchar(max)
select column_name
from information_schema.columns where table_name = 'test1'

declare @crlf varchar(2) = char(13) + char(10)
declare @tab varchar(20) = char(9)
declare @Margin1 varchar(20) = replicate(@tab,@Margin1Tabs)
declare @Margin2 varchar(20) = replicate(@tab,@Margin2Tabs)
declare @namelen int
select @namelen = max(len(column_name)) + @tablen
from information_schema.columns
where table_name = @table

select @namelen = @namelen/@tablen*@tablen+@tablen

select	  @sql1 = coalesce(@sql1 + @crlf + @margin2 + ', ',@margin2 + '  ') + column_name
		, @sql2 = coalesce(@sql2 + @crlf + @margin2 + ', ',@margin2 + '  ') 
					+ column_name
					+ replicate(@tab,(@namelen-len(column_name)-2+3)/4)
					+ '='
					+	case when column_name in ('z_inserted','z_updated') 
								then ' getdate()' 
							else ' t2.' + column_name
						end
from information_schema.columns
where table_name = @table

select	  @sql1 = @Margin1 + 'Insert ' + @table
					+ @crlf + @Margin1 + @tab + '('
					+ @crlf +  @sql1
					+ @crlf +  @Margin1 + @tab + ')'
		, @sql2 = @Margin1 + 'select'
					+ @crlf + @sql2
					+ @crlf + @Margin1 + 'from ' + @table_stg
select @sql1 + @crlf + @sql2
go
/*
result
		Insert test1
			(
				  ID1
				, ID2
				, col1
				, col02
				, col003
				, col0004
				, z_inserted
				, z_updated
			)
		select
				  ID1			= t2.ID1
				, ID2			= t2.ID2
				, col1			= t2.col1
				, col02			= t2.col02
				, col003		= t2.col003
				, col0004		= t2.col0004
				, z_inserted	= t2.z_inserted
				, z_updated		= t2.z_updated
		from test1_stg
*/


home