萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server、Access、Excel 三方資料互導完整指令集

一、SQL Server ↔ Access

1. 使用 DTS 匯入精靈 (圖形介面)

  1. 企業管理員 → 工具 → 資料轉換服務 → 匯入資料
  2. 資料來源選 Microsoft Access,指定 .mdb 路徑
  3. 目的地選 Microsoft OLE DB Provider for SQL Server
  4. 選擇「複製資料表」,全選 → 完成

2. T-SQL 直接查詢 Access (OPENROWSET / OPENDATASOURCE)

-- 查詢 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;

二、SQL Server ↔ Excel

1. 查詢 Excel 工作表

SELECT * FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Book1.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0'
)...[Sheet1$];

注意:工作表名需加 $ 並用方括號括起,如 [Sheet1$];具名區域可直接用名稱。

2. Excel 匯入 SQL Server

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$];

3. SQL Server 匯出至 Excel (bcp)

-- 匯出整張表
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 伺服器/帳號/密碼。

4. 寫入 Excel (OPENROWSET)

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);

三、VB6 範例:ADO 呼叫 bcp 匯出 Excel

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,效能最佳。

copyright © 萬盛學電腦網 all rights reserved