-- 查詢 Access 資料
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\DB.mdb";User ID=Admin;Password='
)...TableName;
-- 將 Access 匯入 SQL Server (建立新表)
SELECT * INTO NewTable FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\DB.mdb";User ID=Admin;Password='
)...TableName;
-- 將 SQL Server 資料寫入 Access
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\DB.mdb";User ID=Admin;Password='
)...TableName (Col1, Col2)
SELECT Col1, Col2 FROM SqlTable;
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Book1.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0'
)...[Sheet1$];
注意:工作表名需加 $ 並用方括號括起,如 [Sheet1$];具名區域可直接用名稱。
SELECT * INTO NewTable FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Book1.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0'
)...[Sheet1$];
-- 匯出整張表
EXEC master..xp_cmdshell 'bcp DbName.dbo.TableName OUT C:\Temp.xls -c -q -S"Server" -U"sa" -P"pwd"';
-- 匯出查詢結果
EXEC master..xp_cmdshell 'bcp "SELECT Col1, Col2 FROM DbName.dbo.TableName ORDER BY Col2" QUERYOUT C:\Authors.xls -c -S"Server" -U"sa" -P"pwd"';
參數:-c 字元模式、-q 引用識別碼、-S/U/P 伺服器/帳號/密碼。
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Temp.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0'
)...[Sheet1$] (ColA, ColB, ColC)
VALUES (1, 2, 3);
Dim cn As New ADODB.Connection
cn.Open "Driver={SQL Server};Server=WEBSVR;Database=WebMis;UID=sa;PWD=123;"
cn.Execute "master..xp_cmdshell 'bcp ""SELECT Col1, Col2 FROM DbName.dbo.TableName"" QUERYOUT E:\DT.xls -c -SServer -Usa -Ppassword'"
善用 OPENDATASOURCE/OPENROWSET 可在 T-SQL 直接跨來源查詢/寫入;大量資料移轉首選 bcp 或 SSIS/DTS,效能最佳。