SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

 

 

CREATE proc pGetInsertSQL (@TableName VARCHAR(256))

SET nocount ON

DECLARE @sqlstr VARCHAR(4000)

DECLARE @sqlstr1 VARCHAR(4000)

DECLARE @sqlstr2 VARCHAR(4000)

SELECT @sqlstr='select ''insert '+@tablename

SELECT @sqlstr1=''

SELECT @sqlstr2=' ('

SELECT @sqlstr1= ' values ( ''+'

SELECT @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' FROM (SELECT CASE

WHEN a.xtype =104 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'

WHEN a.xtype =175 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

WHEN a.xtype =61 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'

WHEN a.xtype =106 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+CONVERT(VARCHAR(4),a.xprec+2)+'),'+a.name +')'+' end'

WHEN a.xtype =62 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'

WHEN a.xtype =56 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'

WHEN a.xtype =60 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'

WHEN a.xtype =239 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

WHEN a.xtype =108 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+CONVERT(VARCHAR(4),a.xprec+2)+'),'+a.name +')'+' end'

WHEN a.xtype =231 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

WHEN a.xtype =59 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'

WHEN a.xtype =58 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'

WHEN a.xtype =52 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'

WHEN a.xtype =122 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'

WHEN a.xtype =48 THEN 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'

WHEN a.xtype =167 THEN 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

ELSE '''NULL'''

END AS col,a.colid,a.name

FROM syscolumns a WHERE a.id = object_id(@tablename) AND a.xtype <>189 AND a.xtype <>34 AND a.xtype <>35 AND a.xtype <>36

)t ORDER BY colid

 

SELECT @sqlstr=@sqlstr+LEFT(@sqlstr2,len(@sqlstr2)-1)+') '+LEFT(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename

EXEC( @sqlstr)

SET nocount off

 

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

 

 

CREATE TABLE test(name VARCHAR(20))

INSERT test

SELECT '1' UNION ALL

SELECT '2' UNION ALL

SELECT '3' UNION ALL

SELECT '4'

 

EXEC pGetInsertSQL 'test'

 

INSERT test (name) VALUES ( '1')

INSERT test (name) VALUES ( '2')

INSERT test (name) VALUES ( '3')

INSERT test (name) VALUES ( '4')