Shulou(Shulou.com)05/31 Report--

This article is about how to write the import and export of SQL statements. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

SQL statement Import and Export Daquan

/ * Export to excel

EXEC master..xp_cmdshell 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+"; User ID=Admin;Password=;Extended properties=Excel 5.0

Set @ s = SELECT * FROM OpenDataSource (+ @ s +)... sheet1 $

Exec (@ s)

, /

SELECT cast (cast (account No. As numeric (10Power2)) as nvarchar (255) + converted alias

FROM OpenDataSource (Microsoft.Jet.OLEDB.4.0

Data Source= "c: est.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0)... xactions

/ * * EXCEL leads to remote SQL

Insert OPENDATASOURCE (

SQLOLEDB

Data Source= remote ip;User ID=sa;Password= password

)。 Library name. Dbo. Table name (column 1, column 2)

SELECT column 1, column 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_cmdshell bcp dbname..tablename in c:DT.txt-c-Sservername-Usa-Ppassword

/ * * Export text file

EXEC master..xp_cmdshell bcp dbname..tablename out c:DT.txt-c-Sservername-Usa-Ppassword

Or

EXEC master..xp_cmdshell bcp "Select * from dbname..tablename" queryout c:DT.txt-c-Sservername-Usa-Ppassword

Export to TXT text, separated by commas

Exec master..xp_cmdshell bcp "library name. Table name" out "d: t.txt"-c-t,-U sa-P password

BULK INSERT library 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

Select * from openrowset (MSDASQL

Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:

Select * from [aa.DBF])

-- * /

/ * Import 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 an already generated structure (that is, an existing) 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

Description:

SourceDB=c: specify the folder where the foxpro table is located

Aa.DBF specifies the file name of the foxpro table.

/ * Export to Access*/

Insert into openrowset (Microsoft.Jet.OLEDB.4.0

Table A) select * from database name.. Table B

/ * Import Access*/

Insert into B table selet * from openrowset (Microsoft.Jet.OLEDB.4.0

XRV A.mdbtern adminten table A)

The file name is parameter

Declare @ fname varchar (20)

Set @ fname = d: est.mdb

Exec (SELECT a.* FROM opendatasource (Microsoft.Jet.OLEDB.4.0)

+ @ fname+;admin;, topics) as a)

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 file

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

?

/ * * Excel imports to Txt**/

Want to use

Select * into opendatasource (...) From opendatasource (...)

Implement to import the contents of an Excel file into a text file

Suppose there are two columns in the Excel, the first column is the name, and the second column is the special line account (16 digits)

And after the bank account is exported to a text file, it is divided into two parts, the first 8 bits and the last 8 bits are separated.

Zou Jian:

If you want to insert with the above sentence, the text file must exist and have a line: name, bank account number 1, bank account number 2

You can then insert it with the following statement

Note that the file name and directory are modified according to your actual situation.

Insert into

Opendatasource (MICROSOFT.JET.OLEDB.4.0

, Text;HDR=Yes;DATABASE=C:

). [aa#txt]

-, aa#txt)

-- * /

Select name, bank account number 1=left (bank account number, 8), bank account number 2=right (bank account number, 8)

From

Opendatasource (MICROSOFT.JET.OLEDB.4.0

, Excel 5.0, HDR, HDR

--, Sheet1 $)

). [Sheet1 $]

If you want to insert and generate a text file directly, use bcp

Declare @ sql varchar (8000), @ tbname varchar (50)

First import the contents of the excel table into a global temporary table

Select @ tbname= [# # temp+cast (newid () as varchar (40)) +]

@ sql=select name, bank account number 1=left (bank account number, 8), bank account number 2=right (bank account number, 8)

Into + @ tbname+ from

Opendatasource (MICROSOFT.JET.OLEDB.4.0

, Excel 5.0, HDR, HDR

). [Sheet1 $]

Exec (@ sql)

-- then export from a global temporary table to a text file using bcp

Set @ sql=bcp "+ @ tbname+" out "c:aa.txt" / S "(local)" / P "" / c

Exec master..xp_cmdshell @ sql

-Delete temporary tables

Exec (drop table + @ tbname)

/ * Guide the entire database * /

Stored procedure implemented with bcp

/ *

A stored procedure that implements data import / export

According to different parameters, you can import / export the entire database / single table.

Call example:

-- example of export call

-Export a single table

Exec file2table zj,xzkh_sa.. Regional information, cJV zj.txtj1

-Export the entire database

Exec file2table zj,xzkh_sa,C:docman,1

-- example of import call

-Import a single table

Exec file2table zj,xzkh_sa.. Regional information, cJV zj.txtj0

-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, which is empty if NT authentication is used

, @ password varchar (200)-password

, @ tbname varchar (500)-- Database. Dbo. Table name, if you do not specify:. Dbo. Table name, all user tables of the database are exported

, @ filename varchar (1000)-- Import / export path / file name. If the @ tbname parameter indicates that the entire database is exported, this parameter is the file storage path, and the file name automatically uses the table name .txt.

, @ isout bit-- 1 is export, 0 is import

As

Declare @ sql varchar 8000

If @ tbname like%.%.%-if a table name is specified, a single table is directly exported

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_cmdshell @ sql

End

Else

Begin-- export the entire database, define cursors, and fetch places

Thank you for reading! On "SQL sentence import and export how to write" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.