SQL Statement Import Export Encyclopedia

Introduction: Microsoft SQL Server database SQL Statement Import Export encyclopedia, including data with other databases and files import export.

/******* 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