Reprinted from: http://hi.baidu.com/pigarmy/blog/item/109894c445eab0a28326ac5a.html
Recently, you need to export data from one table and generate an INSERT statement, discovering that SQL Server's own tools and meters have this capability. Baidu to get the following method (Pro-Test OK)
With this stored procedure you can implement:
CREATE PROCEDURE dbo. Uspoutputdata
@tablename sysname
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--Determines whether an object exists
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 if the object is a table
Begin
print ' The object is not table '
Return
End
Select @ident =status&0x80 from syscolumns where[email protected] and status&0x80=0x80
If @ident is not null
print ' SET identity_insert ' [email protected]+ ' on '
DECLARE syscolumns_cursor cursor
For select C.name,c.xtype from syscolumns c where [e-mail Protected]order by C.colid
Open Syscolumns_cursor
Set @column = ' '
Set @columndata = ' '
FETCH NEXT from Syscolumns_cursor to @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 temporarily does not process
Begin
Set @[email protected]+case when Len (@column) =0 then ' Else ', ' [email protected]
Set @[email protected]+case when Len (@columndata) =0 then ' Else ', ' ', ' ', '
End
+case when @xtype into (167,175) then "" "" + "[email protected]+ ' + '" "" "--varchar,char
When @xtype in (231,239) then "N" "+" [email protected]+ ' + ' "" "--nvarchar,nchar
When @xtype =61 and then "" "+convert (char), ' [email protected]+ ', 121] + '" ""--datetime
When @xtype =58 and then "" +convert (char), ' [email protected]+ ', + + '] + ' "'--smalldatetime
When @xtype =36 and then "" "+convert (char), ' [email protected]+ ') + '" "'--uniqueidentifier
else @name End
End
End
FETCH NEXT from Syscolumns_cursor to @name, @xtype
End
Close Syscolumns_cursor
Deallocate syscolumns_cursor
Set @sql = ' Set NOCOUNT on select ' ' Insert ' [email protected]+ ' (' [email protected]+ ') VALUES (' as '--', ' [email protected] + ', ') ' from ' [email protected]
print '--' [email protected]
EXEC (@sql)
If @ident is not null
print ' SET identity_insert ' [email protected]+ ' OFF '
GO
How to use:
EXEC uspoutputdata your table name
Then save the running result as. sql, and then open execution with SQL Server Management Studio OK!