Microsoft SQL Server SQL statement Import and Export

Reference: http://www.yesky.com/searchdatabase/504969436818571264/20050524/1951934.shtml

 

Overview: SQL statements of the Microsoft SQL Server database are imported and exported, including data imported and exported with other databases and files.

/****** 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] ')
-- */