SQL Server 與 Excel,Access 資料表的匯入匯出(註：參照博園.NET大觀)

 

我們知道在Sql Server 中 整合了資料的匯入匯出這麼一個工具，那用Sql 指令碼怎麼輕鬆操作 Access 和 Excel 中的資料呢，接下來我們看一看：

一、 SQL SERVER 和EXCEL的資料匯入匯出，

A 查詢Excel裡的資料

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

簡單解析：

OpenDataSource ，一個系統裡的函數，如果不是很清楚可以點這個，http://msdn.microsoft.com/zh-cn/library/ms179856.aspx，

簡單的理解就是提供一個特殊的串連，在這裡我們可以理解成開啟Access,和Excel的串連。

上面的指令碼也可以寫成下面這樣，

SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\test.xls";Extended properties=Excel 5.0')...Sheet1$

執行結果如：

B 將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$]

C 將SQL SERVER中查詢到的資料導成一個Excel檔案

T-SQL代碼：

EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Temp.xls -c -q -S"servername" -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"'

 

二、SQL SERVER 和Accss的資料匯入匯出

　　A 在SQL SERVER裡查詢access資料:

SELECT *

FROM OpenDataSource（ 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\DB.mdb";User ID=Admin;Password='）...表名

　　B. 將access匯入SQL server

在SQL SERVER 裡運行:

SELECT *

INTO newtable

FROM OPENDATASOURCE （'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\DB.mdb";User ID=Admin;Password=' ）...表名

　　C. 將SQL SERVER表裡的資料插入到Access表中

　　在SQL SERVER 裡運行：

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 sqltablename

如果運行時，出現如下錯誤如：

訊息 15281，層級 16，狀態 1，第 1 行
SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細資料，請參閱 SQL Server 聯機叢書中的 "介面區配置器"。

有兩種解決辦法（我現在知道的）

1 可以採用以下通過啟用Ad Hoc Distributed Queries解決：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

 使用完成後，關閉Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

 

2 種是 在“開始”——》“所有程式”——》”Microsoft SQL Server 2005 “ 的 ”組態工具“ ——》”SQL Server 介面區配置器“，開啟以後如：

 

然後點擊，如：

 

 然後在”啟用 OPENROWSET 和 OPENDATASOURCE 支援“打上鉤“，確定即可，如：