Stored Procedure for ms SQL Server to export data to insert statements

<Doctype HTML public-wcdtd XHTML stricten httpwwwworgtrxhtmldtdxhtml-strictdtd>

If exists (select * From sysobjects where type = 'p' and name = 'uspoutputdata ')
Drop proc uspoutputdata
Go
Create procedure DBO. uspoutputdata
@ Tablename sysname
As
Declare @ column varchar (2000)
Declare @ columndata varchar (2000)
Declare @ SQL varchar (8000)
Declare @ xtype tinyint
Declare @ name sysname
Declare @ objectid int
Declare @ objectname sysname
Declare @ ident int
Set nocount on
-- Judge ?? Whether the image exists
Set @ objectid = object_id (@ tablename)
If @ objectid is null
Begin
Print 'the object not exists'
Return
End
-- This judgment is not strict
Set @ objectname = rtrim (object_name (@ objectid ))
If @ objectname is null or charindex (@ objectname, @ tablename) = 0
Begin
Print 'object not in current database'
Return
End
-- Judge ?? Whether the image is a table
If objectproperty (@ objectid, 'istable') <> 1
Begin
Print 'the object is not table'
Return
End
-- Do not know the meaning of Printing
Select @ ident = Status & 0x80 from syscolumns where 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 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)
-- Do not know the meaning of Printing
If @ ident is not null
Print 'set identity_insert '+ @ tablename + 'off'
Go

Exec uspoutputdata t_sys_dict_data

Finally, execute the stored procedure using exec uspoutputdata t_sys_dict_data. T_sys_dict_data is the table name and can be used in any table.

# Database Technology