If exists (select * from sysobjects where name = 'proc _ insert ')
Drop proc proc_insert
Go
Create proc proc_insert (@ tablename varchar (256 ))
As
Begin
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. xtype = 173 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. length * 2 + 2) + '),' +. name + ')' + 'end'
When. xtype = 127 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (20), '+. name + ')' + 'end'
When. xtype = 104 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (1), '+. name + ')' + 'end'
When. xtype = 175 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 61 Then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'convert (varchar (23),' +. name + ', 121)' + ''' + 'end'
When. xtype = 106 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. xprec + 2) + '),' +. name + ')' + 'end'
When. xtype = 62 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (23), '+. name + ', 2)' + 'end'
When. xtype = 56 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (11), '+. name + ')' + 'end'
When. xtype = 60 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (22), '+. name + ')' + 'end'
When. xtype = 239 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 108 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. xprec + 2) + '),' +. name + ')' + 'end'
When. xtype = 231 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 59 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (23), '+. name + ', 2)' + 'end'
When. xtype = 58 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'convert (varchar (23),' +. name + ', 121)' + ''' + 'end'
When. xtype = 52 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (12), '+. name + ')' + 'end'
When. xtype = 122 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (22), '+. name + ')' + 'end'
When. xtype = 48 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (6), '+. name + ')' + 'end'
-- When. xtype = 165 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. length * 2 + 2) + '),' +. name + ')' + 'end'
When. xtype = 167 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
Else '''null '''
End as col, a. colid, a. name
From syscolumns A where. id = object_id (@ tablename) and. xtype <> 189 and. xtype <> 34 and. xtype <> 35 and. xtype <> 36
) T order by colid
Select @ sqlstr = @ sqlstr + Left (@ sqlstr2, Len (@ sqlstr2)-1) + ')' + Left (@ sqlstr1, Len (@ sqlstr1)-3) + ') ''from' + @ tablename
-- Print @ sqlstr
Exec (@ sqlstr)
Set nocount off
End
Go
-- Exec proc_insert entry_list -- execute the stored procedure. You only need to specify the table name.