/****** 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
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
/** 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 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)
* ********************* Import XML files
Declare @ IDOC int
Declare @ Doc varchar (1000)
-- Sample XML document
Set @ Doc ='
<Root>
<Customer cid = "C1" name = "Janine" city = "issaquah">
<Order OID = "O1" date = "1/20/1996" amount = "3.5"/>
<Order OID = "O2" date = "4/30/1997" amount = "13.4"> customer was very satisfied
</Order>
</Customer>
<Customer cid = "C2" name = "Ursula" city = "oelde">
<Order OID = "O3" date = "7/14/1999" amount = "100" note = "wrap it blue
White red ">
<Urgency> important </urgency>
Happy customer.
</Order>
<Order OID = "O4" date = "1/20/1996" amount = "10000"/>
</Customer>
</Root>
'
-- 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
ZOU Jian's
/******************* Guide the entire database **************** *****************************/
Stored procedure implemented with BCP
/*
Stored Procedure for Data Import/Export
According Different parameters can be used to import/export the entire database/single table
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
/********************** 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.
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)