Sqlserver export table content to insert statement reprint

SETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGOifexists (select1fromsysobjectswhereBicashyOutputData) andxtypeP) dropprocedureBicashyOutputData; GOcreateproceduredbo. BicashyOutputData (@ tablenamevarchar (256), @ whereStrvarchar

Export GO into GO ifexists (response = object_id ('bicashyoutputdata') andxtype = 'P') dependencies; GO createproceduredbo. BicashyOutputData (@ tablenamevarchar (256), @ whereStrvarchar

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If exists (select 1 from sysobjects where id = object_id ('bicashyoutputdata') and xtype = 'P ')
Drop procedure BicashyOutputData;
GO
Create procedure dbo. BicashyOutputData (@ tablename varchar (256), @ whereStr varchar (256 ))
AS
Declare @ column varchar (1000)
Declare @ columndata varchar (1000)
Declare @ SQL varchar (4000)
Declare @ xtype tinyint
Declare @ name sysname
Declare @ objectId int
Declare @ objectname sysname
Declare @ ident int

Set nocount on
Set @ objectId = object_id (@ tablename)

If @ objectId is null -- determine whether the object existence
Begin
Print 'the object not exists'
Return
End
Set @ objectname = rtrim (object_name (@ objectId ))

If @ objectname is null or charindex (@ objectname, @ tablename) = 0 -- this judgment is not strict
Begin
Print 'object not in current database'
Return
End

If OBJECTPROPERTY (@ objectId, 'istable') <> 1 -- determine whether the object is a table
Begin
Print 'the object is not table'
Return
End

Select @ ident = status & 0x80 from syscolumns where and status & 0x80 = 0x80

If @ ident is not null
Print 'set IDENTITY_INSERT on'

Declare syscolumns_cursor cursor

For select c. name, c. xtype from syscolumns c where order by c. colid

Open syscolumns_cursor
Set @ column =''
Set @ columndata =''
Fetch next from syscolumns_cursor into @ name, @ xtype

While @ fetch_status <>-1
Begin
If @ fetch_status <>-2
Begin
If @ xtype not in (189,34, 35,99, 98) -- timestamp does not need to be processed. image, text, ntext, SQL _variant will not be processed for the moment.

Begin
Set @ column = @ column + case when len (@ column) = 0 then 'else', 'end + @ name

Set @ columndata = @ columndata + case when len (@ columndata) = 0 then ''else ','','','
End

+ Case when @ xtype in (167,175) then -- varchar, char
When @ xtype in (231,239) then -- nvarchar, nchar
When @ xtype = 61 then ''' + convert (char (23), '+ @ name +', 121) + ''' -- datetime
When @ xtype = 58 then ''' + convert (char (16), '+ @ name +', 120) + ''' -- smalldatetime
When @ xtype = 36 then ''' + convert (char (36), '+ @ name + ') + ''' -- uniqueidentifier
Else @ name end

End

End

Fetch next from syscolumns_cursor into @ name, @ xtype

End

Close syscolumns_cursor
Deallocate syscolumns_cursor

Set @ SQL = 'set nocount on select ''' insert) values (''as ''--'', '+ @ columndata +', '');'' from

Print
Exec (@ SQL)

If @ ident is not null
Print 'set IDENTITY_INSERT off'


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

After a stored procedure is created, you can call the stored procedure to view the generated insert statement.

Specific operations:

Execute exec BicashyOutputData cardinfo, 'where drawOutper = "Li pejuan "'

Note that cardinfo is the name of the table to be executed, and 'where drawOutper = "Li pejuan" 'is the where condition. If you do not need to export all the query conditions, you can directly write it as exec BicashyOutputData cardinfo,''

Note that if the query condition contains a string, you must add "" Before and After the string.