SQL Server exports data to scripts

SQL Server exports data to scripts

(1)

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 the 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 -- determines 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

 

(2)

Declare @ objectid int
Set @ objectid = object_id ('proc _ insert ')
If @ objectid is not null
Begin
Drop proc proc_insert
End
Go

Create proc proc_insert (@ tablename varchar (256 ))
As
Begin
Set nocount on
Declare @ sqlstr varchar (4000)
Declare @ sqlstr1 varchar (4000)
Declare @ sqlstr2 varchar (4000)
Select @ sqlstr = 'select' Insert '+ @ tablename
Select @ sqlstr1 =''
Select @ sqlstr2 = '('
Select @ sqlstr1 = 'values (''+'
Select @ sqlstr1 = @ sqlstr1 + Col + '+ '','' +', @ sqlstr2 = @ sqlstr2 + name + ', 'From (select case
When. xtype = 173 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. length * 2 + 2) + '),' +. name + ')' + 'end'
When. xtype = 104 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (1), '+. name + ')' + 'end'
When. xtype = 175 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 61 Then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'convert (varchar (23),' +. name + ', 121)' + ''' + 'end'
When. xtype = 106 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. xprec + 2) + '),' +. name + ')' + 'end'
When. xtype = 62 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (23), '+. name + ', 2)' + 'end'
When. xtype = 56 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (11), '+. name + ')' + 'end'
When. xtype = 60 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (22), '+. name + ')' + 'end'
When. xtype = 239 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 108 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. xprec + 2) + '),' +. name + ')' + 'end'
When. xtype = 231 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
When. xtype = 59 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (23), '+. name + ', 2)' + 'end'
When. xtype = 58 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'convert (varchar (23),' +. name + ', 121)' + ''' + 'end'
When. xtype = 52 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (12), '+. name + ')' + 'end'
When. xtype = 122 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (22), '+. name + ')' + 'end'
When. xtype = 48 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar (6), '+. name + ')' + 'end'
When. xtype = 165 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. length * 2 + 2) + '),' +. name + ')' + 'end'
When. xtype = 167 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'
Else '''null '''
End as COL, A. colid, A. Name
From syscolumns A where. id = object_id (@ tablename) and. xtype <> 189 and. xtype <> 34 and. xtype <> 35 and. xtype <> 36
) T order by colid
Select @ sqlstr = @ sqlstr + Left (@ sqlstr2, Len (@ sqlstr2)-1) + ')' + Left (@ sqlstr1, Len (@ sqlstr1)-3) + ') ''from' + @ tablename
Print @ sqlstr
Exec (@ sqlstr)
Set nocount off
End
Go
-- Execution method (replace tablename with the corresponding data table name ):
Exec proc_insert 'indicates'

 

 

 

-- Use cursors to cycle user tables

Declare @ tablename varchar (20)
Declare cur cursor
Select name from sysobjects where xtype = 'U'
-- Open the cursor
Open cur
Fetch next from cur into @ tablename

While (@ fetch_status = 0)
Begin
Exec ('select * from'+ @ Tablename)
Fetch next from cur into @ tablename
End

Close cur
Deallocate cur