SQL statement Import and Export
/****** Export to excel
Exec master.. xp_mongoshell 'bcp settledb. DBO. shanghu out C:/temp1.xls-C-Q-s "gnetdata/gnetdata"-U "sa"-P ""'
/*********** Import Excel
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
/* Dynamic file name
Declare @ FN varchar (20), @ s varchar (1000)
Set @ fn = 'C:/test.xls'
Set @ s = '''microsoft. Jet. oledb.4.0 '',
''Data source = "'+ @ FN +'"; user id = admin; Password =; extended properties = Excel 5.0 '''
Set @ s = 'select * From OpenDataSource ('+ @ s +')... sheet1 $'
Exec (@ s)
*/
Select cast (subject number as numeric (255) as nvarchar () + ''converted alias
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
/*********************** Export to remote SQL
Insert OpenDataSource (
'Sqlodb ',
'Data source = remote IP address; user id = sa; Password = password'
). Database Name. DBO. Table Name (column name 1, column name 2)
Select column name 1, column name 2
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
/** Import a text file
Exec master .. xp_mongoshell 'bcp dbname .. tablename in C:/dt.txt-C-sservername-USA-ppassword'
/** Export a text file
Exec master .. xp_mongoshell 'bcp dbname .. tablename out C:/dt.txt-C-sservername-USA-ppassword'
Or
Exec master .. xp_mongoshell 'bcp "select * From dbname .. tablename" queryout C:/dt.txt-C-sservername-USA-ppassword'
Export to TXT text, separated by commas
Exec master.. xp_mongoshell 'bcp "database name... table name" out "D:/tt.txt"-c-t,-U sa-P password'
Bulk insert database name... table name
From 'C:/test.txt'
With (
Fieldterminator = ';',
Rowterminator = '/N'
)
--/* Dbase iv File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase IV; HDR = no; IMEX = 2; database = C:/', 'select * from [customer profile 4.dbf]')
--*/
--/* Dbase iii File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase III; HDR = no; IMEX = 2; database = C:/', 'select * from [customer profile 3.dbf]')
--*/
--/* FoxPro Database
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C :/',
'Select * from [AA. DBF] ')
--*/
/************** Import the DBF File ****************/
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver;
Sourcedb = E:/vfp98/data;
Sourcetype = DBF ',
'Select * from customer where country! = "USA" order by country ')
Go
/**************** Export to DBF ***************/
If you want to export data to the generated structure (that is, the existing table) FoxPro table, you can directly use the following SQL statement
Insert into OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C :/',
'Select * from [AA. DBF] ')
Select * from table
Note:
Sourcedb = C:/Specify the folder where the Foxpro table is located
AA. DBF specifies the name of The FoxPro table.
********************/
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'X:/a. mdb '; 'admin'; '', table A) Select * from database name... Table B
/************* Import access ********************/
Insert into B Table selet * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'X:/a. mdb '; 'admin'; '', Table)
The file name is a parameter.
Declare @ fname varchar (20)
Set @ fname = 'd:/test. MDB'
Exec ('select A. * From OpenDataSource (''microsoft. Jet. oledb.4.0 '',
''' + @ Fname + '''; ''admin''; ''', topics) as ')
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "F:/northwind. mdb"; Jet oledb: Database Password = 123; user id = admin; Password =; ')... product
* ********************* Import XML files
Declare @ IDOC int
Declare @ Doc varchar (1000)
-- Sample XML document
Set @ Doc ='
Customer was very satisfied
Important
Happy customer.
'
-- Create an internal representation of the XML document.
Exec sp_xml_preparedocument @ IDOC output, @ Doc
-- Execute a SELECT statement using openxml rowset provider.
Select *
From openxml (@ IDOC, '/root/customer/order', 1)
With (OID char (5 ),
Amount float,
Comment ntext 'text ()')
Exec sp_xml_removedocument @ IDOC
???????
/********************** Export to TXT ************* ***************************/
Intended Use
Select * into OpenDataSource (...) from OpenDataSource (...)
Import an Excel file to a text file
Suppose there are two columns in Excel, the first column is name, and the second column is a very row account (16 digits)
The exported bank accounts are divided into two parts: the first eight digits and the last eight digits.
ZOU Jian:
If you want to insert the statement above, the text file must exist and have one line: name, bank account 1, bank account 2
Then you can use the following statement to insert
Note that the file name and directory are modified according to your actual situation.
Insert
OpenDataSource ('Microsoft. Jet. oledb.4.0'
, 'Text; HDR = yes; database = C :/'
)... [Aa # TXT]
--, Aa # txt)
--*/
Select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8)
From
OpenDataSource ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; IMEX = 2; database = C:/a.xls'
--, Sheet1 $)
)... [Sheet1 $]
If you want to directly insert and generate a text file, you need to use BCP
Declare @ SQL varchar (8000), @ tbname varchar (50)
-- First import the Excel table content to a global temporary table
Select @ tbname = '[# temp' + Cast (newid () as varchar (40) + ']'
, @ SQL = 'select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8)
Into '+ @ tbname +' from
OpenDataSource (''microsoft. Jet. oledb.4.0''
, ''Excel 5.0; HDR = yes; IMEX = 2; database = C:/a.xls''
)... [Sheet1 $]'
Exec (@ SQL)
-- Use bcp to export data from a global temporary table to a text file
Set @ SQL = 'bcp "'+ @ tbname +'" out "C:/aa.txt"/s "(local)"/P ""/C'
Exec master .. xp_mongoshell @ SQL
-- Delete a temporary table
Exec ('drop table' + @ tbname)
/******************* Guide the entire database **************** *****************************/
Stored procedure implemented with BCP
/*
Stored Procedure for Data Import/Export
You can import/export the entire database or a single table based on different parameters.
Call example:
-- Export call example
---- Export a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c:/zj.txt ', 1
---- Export the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c:/docman ', 1
-- Import call example
---- Import a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c:/zj.txt ', 0
---- Import the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c:/docman ', 0
*/
If exists (select 1 from sysobjects where name = 'file2table' and objectproperty (ID, 'isprocedure ') = 1)
Drop procedure file2table
Go
Create procedure file2table
@ Servername varchar (200) -- server name
, @ Username varchar (200) -- user name. If you use the NT authentication method, it is null''
, @ Password varchar (200) -- Password
, @ Tbname varchar (500) -- database. DBO. Table name. If not specified:. DBO. Table Name, all user tables of the database will be exported.
, @ Filename varchar (1000) -- import/export path/file name. If @ tbnameverification indicates that the entire data warehouse is exported, the file name will automatically use table name .txt
, @ Isout bit -- 1 is for export, 0 is for Import
As
Declare @ SQL varchar (8000)
If @ tbname like '%. %. %' -- if the table name is specified, a single table is exported directly.
Begin
Set @ SQL = 'bcp' + @ tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"' + @ Filename + '"/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
End
Else
Begin -- export the entire database, define the cursor, and retrieve all user tables
Declare @ m_tbname varchar (250)
If right (@ filename, 1) '/'set @ filename = @ filename + '/'
Set @ m_tbname = 'Clare # TB cursor for select name from '+ @ tbname +' .. sysobjects where xtype = 'u '''
Exec (@ m_tbname)
Open # TB
Fetch next from # TB into @ m_tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'bcp' + @ tbname + '...' + @ m_tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"'{@Filename}@m_tbname}'.txt"/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
Fetch next from # TB into @ m_tbname
End
Close # TB
Deallocate # TB
End
Go
/************* Oracle **************/
Exec sp_add1_server 'oraclesvr ',
'Oracle 7.3 ',
'Msdaora ',
'Orcldb'
Go
Delete from openquery (mailser, 'select * From yulin ')
Select * From openquery (mailser, 'select * From yulin ')
Update openquery (mailser, 'select * from Yulin where id = 15') set disorder = 555, catago = 888
Insert into openquery (mailser, 'select disorder, catago from Yulin ') values (333,777)
Supplement:
For export with BCP, there is no field name.
To export data using OpenRowSet, you must create a table in advance.
OpenRowSet is used for import. Except for access and Excel, non-local data import is not supported.
Author blog: http://blog.csdn.net/whchen/