SQL Server Export SQL script (INSERT statement for table data)

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!