SQL Server SQL Statement Import Export Encyclopedia

Introduction to the server| statement: Microsoft SQL Server Database SQL statements import the Export encyclopedia, including import and export of data with other databases and files.

/******* Export to Excel
EXEC Master.. xp_cmdshell ' 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 (CAST (account number as numeric (10,2)) as nvarchar (255)) + ' 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 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:\tt.txt "-c-t,-u sa-p password '

BULK INSERT Library 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 Document
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 dbf file ****************/
SELECT * FROM OPENROWSET (' MSDASQL ',
' Driver=microsoft Visual FoxPro Driver;
Sourcedb=e:\vfp98\data;
Sourcetype=dbf ',
' SELECT * from customer where country!= ' The USA ' ORDER by country ')
Go
/***************** exported to DBF ***************/
If you want to export data to an already generated structure (that is, an existing) FoxPro table, you can use the following SQL statement directly

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 resides
Aa. DBF Specifies the filename of the FoxPro table.

/************* Export to access********************/
INSERT INTO OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ',
' X:\A.mdb '; ' Admin '; ', a table) 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 A

Import XML file

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= "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

[1] [2] [3] Next page