CREATE proc sp_ScriptTableInsert
@DatabaseName varchar(500),
@ScriptSourceTable varchar(500),
@DeleteExisting int = 1,
@OutputToFile varchar(1000) = null,
@displayOnly int = 0
as
--Created By :	Nicole Welch						
--Created On :	May 01, 2001						
--Permissions: 	Requires create/drop table permissions in tempdb, select on source table
--Description: 	Create an insert statement with values for each row in the passed table. 
--		It can handle translation of Null values and all data type translation except blob types
--		Uses the primary key to generate either a if exists delete or if not exist before the insert statement
--		If table has an Identity column, it will generate a  set identity_insert statement 
--		Can be compiled in any database and reference a different database 
--		Uses ", so always sets Quoted_Identifier off
--		Always returns script in results pane, optionally to a file
--		Make sure to set the 'Max characters per column' setting  High (8000) in Query Analyzer
--Parameters:			
--		@DatabaseName,      Required, database to script rows from
--		@ScriptSourceTable, Required, table to script rows from
--		@DeleteExisting:    Optional, Default 1, Scripts either an 'if exists delete' or 'if not exists' 
--				    prior to insert based on primary key 
--		@OutputToFile:      Optional, Default Null, Fully Qualified path and filename of script file
--				    Uses the xp_cmdshell and the dos 'echo' command to create the file
--		@displayOnly:	    Displays adhoc code rather than executing it, usefull for debugging.
--
--Call Example:	sp_ScriptTableInsert 
--		@DatabaseName = 'MyDB' ,
--		@ScriptSourceTable= 'City',
--		@DeleteExisting = 1,
--		@OutputToFile= 'd:\temp\CityTableInsertScript.sql'
--Results in: 	
--		set identity_insert City on 
--		if exists(select * from City where CityId=1)  delete from City where CityId=1
--		Insert into City(CityId,City,RE_RegionCode,CO_CountryCode,TimezoneID,AsofDate) 
--		Values ( 2,"Abidjan","","CIV",null,"Jan 12 2001 10:13:10:303AM") 
--		set identity_insert City off 

set nocount on
declare @sql varchar(8000),@oscmd varchar(8000)
declare @PrecolumnString varchar(8000),@PostcolumnString varchar(8000)
declare @columnname varchar(50),@columnlist varchar(8000),@columnString varchar(8000)
declare @PreInsertString varchar(8000),@PostInsertString varchar(8000)
declare @PreValueString varchar(8000),@PostValueString varchar(8000)
declare @valuelist varchar(8000),@values varchar(8000)
declare @table_name varchar(100),@columnposition int,@pkey int,@TableHasIdentity int,@data_type varchar(100)
declare @KeyList varchar(8000),@PreKeyExistList varchar(8000),@PostKeyExistList varchar(8000),@delList varchar(8000)

set @sql =  ''
set @sql = @sql + 'use ' + @DatabaseName + char(10)
set @sql = @sql + 'select count(*) tableexists ' + char(10)
set @sql = @sql + 'into tempdb.dbo.tableexists' +char(10)
set @sql = @sql + 'FROM INFORMATION_SCHEMA.tables' +char(10)
set @sql = @sql + 'where table_name = ''' + @ScriptSourceTable +'''' + char(10)
execute (@sql)
if (select tableexists from tempdb.dbo.tableexists) = 0 
begin
	print 'Table does not exist in this Database'
	drop table tempdb.dbo.tableexists
	return
end

set @sql =  ''
set @sql = @sql + 'use ' + @DatabaseName + char(10)
set @sql = @sql + 'select count(*) rowsexist ' + char(10)
set @sql = @sql + 'into tempdb.dbo.rowsexist' +char(10)
set @sql = @sql + 'FROM ' +@ScriptSourceTable+char(10)
execute (@sql)
if (select rowsexist from tempdb.dbo.rowsexist) = 0 
begin
	drop table tempdb.dbo.tableexists
	drop table tempdb.dbo.rowsexist
	return
end
drop table tempdb.dbo.tableexists
drop table tempdb.dbo.rowsexist

set @sql =  ''
set @sql = @sql + 'use ' + @DatabaseName + char(10)
set @sql = @sql + 'if exists(select name from tempdb.dbo.sysobjects where name = ''tempcolumns'')'+char(10)
set @sql = @sql + 'drop table tempdb.dbo.tempcolumns '+char(10)
set @sql = @sql + 'SELECT c.TABLE_NAME,TableHasIdentity = OBJECTPROPERTY ( object_id(c.table_name),''TableHasIdentity''), '+ char(10)
set @sql = @sql + 'c.COLUMN_NAME,c.ORDINAL_POSITION ColumnPosition,data_type ,'+ char(10)
set @sql = @sql + 'pkey = case when pkeys.column_name is not null then 1 else 0 end,'+ char(10)
set @sql = @sql + 'ColumnString = '+ char(10)
set @sql = @sql + 'case  '+ char(10)
set @sql = @sql + 'WHEN data_type in( ''nvarchar'',''varchar'',''char'',''nchar'') '+ char(10)
set @sql = @sql + 'THEN '+ char(10)
set @sql = @sql + '''case when '' + c.column_name +'' is null then ''''null'''' '' +'+ char(10)
set @sql = @sql + '''else ''''"''''+'' + c.column_name + ''+''''"'''' end'''+ char(10)
set @sql = @sql + 'WHEN data_type in (''smallint'',''tinyint'',''int'',''bit'',''decimal'',''numeric'',''money'',''smallmoney'',''float'',''real'' )'+ char(10)
set @sql = @sql + 'THEN '+ char(10)
set @sql = @sql + '''case when '' + c.column_name +'' is null then ''''null'''' '' +'+ char(10)
set @sql = @sql + '''else convert(varchar(100),'' + c.column_name +'') end'''+ char(10)
set @sql = @sql + 'WHEN data_type in (''datetime'',''smalldatetime'')'+ char(10)
set @sql = @sql + 'THEN ''case when '' + c.column_name +'' is null then ''''null'''' '' +'+ char(10)
set @sql = @sql + '''else ''''"''''+convert(varchar(30),'' + c.column_name +'',109) +''''"'''' end'''+ char(10)
set @sql = @sql + 'end'+ char(10)
set @sql = @sql + 'into tempdb.dbo.tempcolumns'+ char(10)
set @sql = @sql + 'FROM INFORMATION_SCHEMA.columns c'+ char(10)
set @sql = @sql + 'left join ('+ char(10)
set @sql = @sql + '	SELECT TABLE_NAME ,COLUMN_NAME,ORDINAL_POSITION '+ char(10)
set @sql = @sql + '	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE'+ char(10)
set @sql = @sql + '	where CONSTRAINT_NAME like ''pk%'''+ char(10)
set @sql = @sql + ') as pkeys on c.table_name = pkeys.table_name and c.column_name = pkeys.column_name'+ char(10)
set @sql = @sql + 'where c.TABLE_NAME = ''' + @ScriptSourceTable + ''''+ char(10)
set @sql = @sql + 'and data_type not in (''text'',''ntext'',''image'')' + char(10)
set @sql = @sql + 'order by c.TABLE_NAME ,c.ORDINAL_POSITION'+ char(10)
if @displayOnly = 1 select (@sql) else execute (@sql)
set @sql =  ''
set @sql = @sql + 'declare columnlist cursor global FAST_FORWARD for '+ char(10)
set @sql = @sql + 'select table_name,TableHasIdentity,column_name,columnposition,data_type,pkey,columnString '+ char(10)
set @sql = @sql + 'from tempdb.dbo.tempcolumns '+ char(10)
if @displayOnly = 1 select (@sql) else execute (@sql)

set @columnlist = ''
set @valuelist = ''
set @PreInsertString = '' 
set @PostInsertString = ''
open global columnlist
FETCH NEXT FROM global columnlist into @table_name,@TableHasIdentity,@columnname,@columnposition,@data_type,@pkey,@columnString
if @TableHasIdentity = 1
begin
	set @PreInsertString = 'set identity_insert ' + @table_name + ' on ' + char(10)
	set @PostInsertString = 'set identity_insert ' + @table_name + ' off ' + char(10) 
end
set @PreColumnString = char(10) + 'Insert into ' + @table_name + '(' 
set @PostColumnString = ') ' 
set @PreValueString = char(10) + 'Values ( ' 
set @PostValueString = ') '  + char(10)
WHILE @@FETCH_STATUS = 0
BEGIN

	set @columnlist = @columnlist +  @columnname  
	set @valuelist = @Valuelist +  @columnString  

	FETCH NEXT FROM global columnlist into @table_name,@TableHasIdentity,@columnname,@columnposition,@data_type,@pkey,@columnString
	if @@FETCH_STATUS = 0
	Begin
		set @columnlist = @columnlist + ',' 
		set @valuelist = @Valuelist +  '+'',''+'  
	End
End
CLOSE global columnlist
deallocate global columnlist  

set @sql =  ''
set @sql = @sql + 'declare KeyList cursor global FAST_FORWARD for '+ char(10)
set @sql = @sql + 'select column_name,columnString '+ char(10)
set @sql = @sql + 'from tempdb.dbo.tempcolumns'+ char(10)
set @sql = @sql + 'where pkey = 1 '+ char(10)
if @displayOnly = 1 select (@sql) else execute (@sql)

open global KeyList
FETCH NEXT FROM global KeyList into @columnname,@columnString
set @KeyList = ''
set @KeyList = @KeyList + '' + @columnname + '=''+' +  @columnString
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH NEXT FROM global KeyList into @columnname,@columnString
	if @@FETCH_STATUS = 0
	Begin
		set @KeyList = @KeyList + '+'' and ' + @columnname + '=''+' +  @columnString
	End
End
CLOSE global KeyList
deallocate global KeyList  

if @DeleteExisting = 1
begin
	set @PreKeyExistList = '''if exists(select * from ' + @table_name + ' where '
	set @PostKeyExistList = '+'') ''+' + ''' delete from ' + @table_name + ' where '
	set @DelList = @PreKeyExistList + @KeyList + @PostKeyExistList +  @KeyList + char(10)
end
else
begin
	set @PreKeyExistList = '''if not exists(select * from ' + @table_name + ' where '
	set @PostKeyExistList = '+'')'''
	set @DelList = @PreKeyExistList + @KeyList + @PostKeyExistList  + char(10)
end
set @sql =  ''
set @sql = @sql + 'use ' + @DatabaseName + char(10)
set @sql = @sql + 'declare Valuelist cursor global FAST_FORWARD for '+ char(10)
set @sql = @sql + 'SELECT ' + @DelList + ','+ @ValueList + char(10)
set @sql = @sql + 'FROM ' + @table_name + char(10)
if @displayOnly = 1 select (@sql) else execute (@sql)

print 'set quoted_identifier off'
if datalength(@OutputToFile) > 0	
begin
	select @oscmd = 'exec master..xp_cmdshell ''' + ' echo set quoted_identifier off' + '> ' + @OutputToFile+ ''', NO_OUTPUT'
	if @displayOnly = 1 select (@oscmd) else execute (@oscmd)
end
open global Valuelist
FETCH NEXT FROM global Valuelist into @DELLIST,@Values
WHILE @@FETCH_STATUS = 0
BEGIN
	print @PreInsertString + @delList + 
	@PreColumnString + @columnlist + @PostColumnString + 
	@PreValueString + @values + @PostValueString +
	@PostInsertString
	if datalength(@OutputToFile) > 0
	begin
		set @oscmd = 'exec master..xp_cmdshell ''' + ' echo ' +
		replace(@delList + @PreInsertString + 
		@PreColumnString + @columnlist  + @PostColumnString +  
		@PreValueString + @values + @PostValueString + 	@PostInsertString,char(10),' ')
		+ '>> ' + @OutputToFile+ ''', NO_OUTPUT'
		if @displayOnly = 1 select (@oscmd) else execute (@oscmd)
	end
	FETCH NEXT FROM global Valuelist into @dellist,@Values
End
CLOSE global Valuelist
deallocate global Valuelist  



GO