[T-SQL] General Insert SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[generalInsert] (@SourceTableName varchar(100), @TargetTableName varchar(100) = '', @WhereClause varchar(8000) = '') with recompile as
Begin
-- Description : Generate Insert queries with data
-- Created By : Farhan Iqbal
-- Dated : 3th March, 2007
-- Example : Exec GeneralInsert 'ACCT_Orders', 'ACCT_Orders', 'Where Order_Id = 1'
Declare @Fields varchar(8000), @Data varchar(max), @SQL nvarchar(max)
set @Fields = ' '
set @Data = ''
if len(ltrim(rtrim(@TargetTableName))) = 0 set @TargetTableName = @SourceTableName
select @Fields = @Fields + ','+Column_Name
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @Data = @Data + ', '+ Case
when Data_Type = 'uniqueidentifier' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varbinary' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'timestamp' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sysname' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sql_variant' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'text' then +'''''''+isNull(replace(convert(varchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'ntext' then +'''''''+isNull(replace(convert(nvarchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'image' then +''''''''''
when Data_Type = 'xml' then +''''''''''
when Data_Type = 'nvarchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'nchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'char' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'datetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'smalldatetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'bigint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'binary' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'bit' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'decimal' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'float' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'int' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'money' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'numeric' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'real' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'tinyint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallmoney' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+''' else '' end
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @SQL = 'Select ''Insert into '+@TargetTableName+' ('+ Substring (@Fields, 3, len(@Fields))+')
values ('+ substring(rtrim(ltrim(@Data)), 3, len(@Data))+ ')'' from '+@SourceTableName +' '+@WhereClause
execute sp_executesql @SQL
End