SQL Server 與 Access、Excel 資料互轉實戰

前言

SQL Server 2000 內建 DTS 可導入導出資料，亦可直接使用 Transact-SQL 語句，透過 OpenDataSource、OPENROWSET 函數實現 SQL Server、Access、Excel 間的資料轉換。以下提供實用語法與範例。

一、SQL Server 與 Access 資料互轉

1. 在 SQL Server 查詢 Access 資料

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\DB.mdb";User ID=Admin;Password=')...資料表名

2. 將 Access 導入 SQL Server（建新資料表）

SELECT *
INTO newtable
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\DB.mdb";User ID=Admin;Password=')...資料表名

3. 將 SQL Server 資料插入 Access 表

INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\DB.mdb";User ID=Admin;Password=')...資料表名 (欄位1, 欄位2)
SELECT 欄位1, 欄位2 FROM sql資料表

範例：

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'C:\db.mdb';'admin';'', Test)
SELECT id, name FROM Test

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'c:\trade.mdb';'admin';'', 資料表名)
SELECT * FROM sql資料表名

二、SQL Server 與 Excel 資料互轉

1. 在 SQL Server 查詢 Excel 資料

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

範例：

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

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 5.0')...[Sheet1$]

範例：

SELECT * INTO newtable
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

3. 將 SQL Server 查詢結果導出為 Excel 檔案

使用 bcp 工具：

EXEC master..xp_cmdshell 'bcp 資料庫名.dbo.資料表名 out c:\Temp.xls -c -q -S"伺服器名" -U"sa" -P""'

參數：-S SQL 伺服器名、-U 使用者、-P 密碼。亦可導出文字檔等多種格式。

範例：

EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\authors.xls -c -Sservername -Usa -Ppassword'

VB6 中用 ADO 執行：

Dim cn As New ADODB.Connection
cn.Open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.Execute "master..xp_cmdshell 'bcp ""SELECT col1, col2 FROM 資料庫.dbo.資料表"" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

4. 在 SQL Server 向 Excel 插入資料

INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) VALUES (1,2,3)

-- 或使用 OPENROWSET
INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
  'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test')