SQL Server Insert Stored procedure

--A stored procedure that generates INSERT statements based on data in a table
Create Proc Proc_insert (@tablename varchar (256))
As--table name
Begin
SET NOCOUNT ON
Declare @sqlstr varchar (4000),
@sqlstr1 varchar (4000),
@sqlstr2 varchar (4000)

Select @sqlstr = ' select ' ' Insert ' [email protected]

Select @sqlstr1 = ' Values ' (' + ', @sqlstr2 = ' ('

Select @[email protected]+col+ ' + ', ' + ', @[email protected]+name + ', ' from (Select case

--When A.xtype =173 and then ' case ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), A.L ength*2+2) + '), ' +a.name + ') ' + ' end '

When A.xtype =104 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (1), ' +a.name + ') ' + ' end '

When A.xtype =175 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '

When A.xtype =61 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' + ' + ' convert (varchar), ' +a.name + ', 121 ) ' + ' + ' + ' + ' end '

When A.xtype =106 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), a.xprec+2) + '), ' +a.name + ') ' + ' end '

When A.xtype =62 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar, ' +a.name + ', 2) ' + ' end '

When A.xtype =56 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (one) ', ' +a.name + ') ' + ' end '

When A.xtype =60 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar) ', ' +a.name + ') ' + ' end '

When A.xtype =239 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '

When A.xtype =108 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), a.xprec+2) + '), ' +a.name + ') ' + ' end '

When A.xtype =231 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '

When A.xtype =59 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar, ' +a.name + ', 2) ' + ' end '

When A.xtype =58 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' + ' + ' convert (varchar), ' +a.name + ', 121 ) ' + ' + ' + ' + ' end '

When A.xtype =52 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (), ' +a.name + ') ' + ' end '

When A.xtype =122 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar) ', ' +a.name + ') ' + ' end '

When A.xtype =48 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (6), ' +a.name + ') ' + ' end '

--When A.xtype =165 and then ' case ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), A.L ength*2+2) + '), ' +a.name + ') ' + ' end '

When A.xtype =167 then ' case ', ' +a.name+ ' was 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 @[email protected]+left (@sqlstr2, Len (@sqlstr2)-1) ' +left (@sqlstr1, Len (@sqlstr1)-3) + ') ' from ' [email Protected

EXEC (@sqlstr)

SET NOCOUNT OFF

End

GO

--Call
EXEC proc_insert ' table name '