Database Application-SQL statement Import and Export

/****** Export to excel
EXEC master.. xp_mongoshell bcp SettleDB. dbo. shanghu out c: emp1.xls-c-q-S "GNETDATA/GNETDATA"-U "sa"-P ""

/*********** Import Excel
SELECT *
FROM OpenDataSource (Microsoft. Jet. OLEDB.4.0,
Data Source = "c: est.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0)... xactions

/* Dynamic file name
Declare @ fn varchar (20), @ s varchar (1000)
Set @ fn = c: est.xls
Set @ s = Microsoft. Jet. OLEDB.4.0,
Data Source = "+ @ fn +"; UserID = 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: est.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0)... xactions

/*********************** Export to remote SQL
Insert OPENDATASOURCE (
SQLOLEDB,
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: est.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: t.txt"-c-t,-U sa-P password

Bulk insert database name... table name
FROM c: est.txt
WITH (
FIELDTERMINATOR = ;,
ROWTERMINATOR =
)

--/* 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: VFP98data;
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: est. 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: orthwind. 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 =
<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

/************** 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 data, 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 data, 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. It is null if NT authentication is used.
, @ 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
+