Export the data in the SQL Server table as an insert into script

Resend a dude'sArticleYou are too lazy to take a closer look.

 

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 -- determine whether the object existence
Begin
Print 'the object not exists'
Return
End
Set @ objectname = 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 id = @ objectid and status & 0x80 = 0x80

If @ ident is not null
Print 'set identity_insert '+ @ tablename + 'on'

Declare syscolumns_cursor cursor
For select C. Name, C. xtype from syscolumns C
Where C. ID = @ objectid
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 ''' + '+ @ name +' + ''' -- varchar, char
When @ xtype in (231,239) Then ''' + '+ @ name +' + ''' -- 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' + @ tablename + '(' + @ column + ') values ('as ''--'', '+ @ columndata +', '') 'from' + @ tablename

Print '--' + @ SQL
Exec (@ SQL)

If @ ident is not null
Print 'set identity_insert '+ @ tablename + 'off'

Go

The execution method is exec uspoutputdata, your table name.
Export Data But the image, text, ntext, and SQL _variant Columns cannot appear in the statement, which will be improved later.