To generate a table data insert script The SQL script that generates the INSERT statement
Last Update:2017-02-28
Source: Internet
Author: User
Copy Code The code is as follows:
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--Author: Hua Ling
--Create date:2008-10-28
--Description: Generate 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 A.xtype =173 then ' case when ' +a.name+ ' is null then ' ' null ' else ' + ' convert (varchar (' +convert (4), varchar +2) + ', ' +a.name + ') ' + ' end '
When A.xtype =104 then ' case when ' +a.name+ ' are 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 (), ' +a.name + ', 121) ' + ' + ' ' ', ' ' + ' end '
When A.xtype =106 then ' case when ' +a.name+ ' is null then ' ' null ' else ' + ' convert (varchar (' +convert (4), varchar) + '), ' +a.name + ') ' + ' end '
When A.xtype =62 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ', 2) ' + ' end '
When A.xtype =56 then ' case when ' +a.name+ ' is null then ' ' null ' else ' + ' convert (varchar (one), ' +a.name + ') ' + ' end '
When A.xtype =60 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar), ' +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 (4), varchar) + '), ' +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+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ', 2) ' + ' end '
When A.xtype =58 then ' case when ' +a.name+ ' is null then ' ' null ' else ' + ' ' + ' + ' convert (varchar (), ' +a.name + ', 121) ' + ' + ' ' ', ' ' + ' end '
When A.xtype =52 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ') ' + ' end '
When A.xtype =122 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar), ' +a.name + ') ' + ' end '
When A.xtype =127 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (6), ' +a.name + ') ' + ' end '
When A.xtype =48 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (6), ' +a.name + ') ' + ' end '
When A.xtype =165 then ' case when ' +a.name+ ' is null then ' ' null ' else ' + ' convert (varchar (' +convert (4), varchar +2) + ', ' +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
) 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