SQL Server exports data to scripts

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.