Set ansi_nulls on
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: Wolf
-- Create Date: 2014-06-25
-- Description: generate an insert script for table data.
-- Demo: exec pcreateinsertscript 'Station ', 'stationid = 61'
-- ===================================================== ======
Create proc [DBO]. pcreateinsertscript (@ tablename varchar (256), @ con nvarchar (400 ))
As
Begin
Set nocount on
Declare @ sqlstr varchar (max)
Declare @ sqlstr1 varchar (max)
Declare @ sqlstr2 varchar (max)
Select @ sqlstr = 'select' Insert '[email protected]
Select @ sqlstr1 =''
Select @ sqlstr2 = '('
Select @ sqlstr1 = 'values (''+'
Select @ [email protected] + Col + '+ '',' + ', @ [email protected] + 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 = 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 = 127 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (6), '+. 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 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] + 'where 1 = 1 and' + isnull (@ con ,'')
Print @ sqlstr
Exec (@ sqlstr)
Set nocount off
End