Generate sql INSERT From Data Record

Admin Jeu 10 Juil - 17:08

Code:: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[GenerateInsertStatement] ( @tableName sysname, @newtableName sysname=NULL ) as begin declare @column varchar(200) DECLARE @sql varchar(5000) if @newtableName is null set @newtableName=@tableName set @sql='select ''insert into '+@newtableName+'( ' select @sql=coalesce(@sql+COLUMN_NAME+',','') from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE not in ( 'timestamp','image','sql_variant') and TABLE_NAME=@tableName set @sql=left(@sql,len(@sql)-1)+') select ''+' select @sql=coalesce(@sql+ case when DATA_TYPE='uniqueidentifier' THEN '+isnull(cast('''''''''+COLUMN_NAME+' as nvarchar(75)),''NULL'')+'''''',''+' when DATA_TYPE='varchar' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'','' +' when DATA_TYPE='char' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'',''+' when DATA_TYPE='nvarchar' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'','' +' when DATA_TYPE='nchar' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'','' +' when DATA_TYPE='text' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'','' +' when DATA_TYPE='ntext' THEN 'isnull(''''''''+'+COLUMN_NAME+ '+'''''''',''NULL'')+'','' +' when DATA_TYPE='datetime' THEN 'isnull(''''''''+convert(varchar,'+COLUMN_NAME+ ',121)+'''''''',''NULL'')+'','' +' when DATA_TYPE='smalldatetime' THEN 'isnull(''''''''+convert(varchar,'+COLUMN_NAME+ ',121)+'''''''',''NULL'')+'','' +' else 'isnull('''''''' + cast('+COLUMN_NAME+' as nvarchar)+ '''''''',''NULL'')+'',''+' end,'' ) from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE not in ( 'timestamp','image','sql_variant') and TABLE_NAME=@tableName SET @sql=left(@sql,len(@sql)-3) set @sql=@sql+''' from '+@tableName --print (@sql) exec (@sql) end