導入導出大全(2004-12-13) prefix = o ns = \"urn:schemas-microsoft-com:office:office\" />
http://builderman.home.sunbo.net/show_hdr.php?xname=U53DTV0&dname=517DTV0&xpos=1
、打開另外個數據庫1.
打開SQL server2000
另個數據庫表(1). select * from openrow
(\'sqloledb\',\'sprogram\';\'develop\';\'11111\',\'select * from kind\')(2). select * from openrow
(\'sqloledb\',\'190.1.1.247\';\'develop\';\'11111\',\'select * from kind\')(3). select * from opendatasource(\'sqloledb\',\'data source=sprogram;user ID=develop;Password=11111\').
_ks.dbo.kind2.
打開Access2000
個表(1). select * from OPENDATASOURCE( \'Microsoft.Jet.OLEDB.4.0\',\'Data Source=e:\\north.mdb;User ID=Admin;Password=\')...supply
(2). select * from openrow
(\'Microsoft.Jet.OLEDB.4.0\', \'e:\\north.mdb\';\'admin\';\'\',supply)
(3). select * from openrow
(\'Microsoft.Jet.OLEDB.4.0\', \'e:\\north.mdb\';\'admin\';\'\',\'select * from supply where 供應商編號>10\')
3.
打開Excel
個表(1). select * from OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=e:\\test.xls\',sheet1$)
(2). select *
o 表 from OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
\'Excel 5.0;HDR=YES;DATABASE=e:\\test1.xls\'sheet3$)2、導入與導出
1. 用BCP工具導入導出
(1)xls文件
EXEC master..xp_cmdshell \'bcp \"SELECT
_nokind_nocn_name FROM _ks.dbo.kind ORDER BY 12\" queryout \"e:\\test3.xls\" -c -q -S\"sprogram\" -U\"develop\" -P\"12345\"\'(2)/** 导入文本文件
--入SQLServer
select *
o 你表 from OpenRow(\'MSDASQL\' \'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\\temp;\'\'select * from contact.txt\')--查詢導出
EXEC master..xp_cmdshell \'bcp \"SELECT
_nokind_nocn_name FROM _ks.dbo.kind ORDER BY 12\" queryout \"e:\\TX1.txt\" -c -q -S\"sprogram\" -U\"develop\" -P\"12345\"\'--直接導出
EXEC master..xp_cmdshell \'bcp
_ks.dbo. out \"e:\\TX3.txt\" -c -S\"sprogram\" -U\"develop\" -P\"12345\"\'--直接導入
EXEC master..xp_cmdshell \'bcp \"數據庫.dbo.數據表\" in c:\\DT.txt -c -S服務器名 -U用戶 -P密碼\'
EXEC master..xp_cmdshell \'bcp hdh.dbo.kind in \"e:\\TX1.txt\" -c -q -S -U -P\'
EXEC master..xp_cmdshell \'bcp hdh.dbo.kind in \"e:\\test3.xls\" -c -q -S -U -P\'
--用BULK INSERT導入
BULK INSERT hdh.dbo.kind
FROM \'e:\\tx1.txt\'
WITH (
FIELDTERMINATOR = \'\\t\'
ROWTERMINATOR = \'\\n\'
)
前提條件是表如kind要存在
而且字段與test.txt字段數目要樣多在查詢分析器中運\行
而且是以sa用戶登錄bulk insert tbyhhk from \'c:\\t.txt\' with( formatfile=\'c:\\bcp.txt\')
3.其它數據庫
導入思路方法:
(1)--/* dBase IV文件
select * from
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
\'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\\\'\'select * from [客户资料4.dbf]\')--*/
(2)--/* dBase III文件
select * from
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
\'dBase III;HDR=NO;IMEX=2;DATABASE=C:\\\'\'select * from [客户资料3.dbf]\')--*/
(3)--/* FoxPro 数据库
select * from openrow
(\'MSDASQL\'\'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\\\'
\'select * from [aa.DBF]\')
--*/
最新评论