Generate an Insert script for table data. It is easy to generate an SQL script for Insert statements.

Copy codeThe Code is as follows:
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go
-- ===================================================== ======
-- Author: hualing
-- Create date: 2008-10-28
-- Description: generate an Insert script for table data.
-- Demo: exec pCreateInsertScript 'bexmcodetype ', 'dictypeid = 61'
-- ===================================================== ======
Alter proc [dbo]. pCreateInsertScript (@ tablename varchar (256), @ con nvarchar (400 ))
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 = 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 @ sqlstr = @ sqlstr + left (@ sqlstr2, len (@ sqlstr2)-1) + ')' + left (@ sqlstr1, len (@ sqlstr1)-3) + ') ''from' + @ tablename + 'where 1 = 1 and' + isnull (@ con ,'')
Print @ sqlstr
Exec (@ sqlstr)
Set nocount off
End