Execl(2003)資料 匯入 SQL Server(2005)

最近在做這個問題的時候，發現網上很多資料不是很全，而且有些是有錯的，現在我把問題解決了，特把相應的方法貼出來。

 

在執行插入語句前，需要做下面幾步操作：

1、從開始選單進入到 配置工具 > sql server 2005 外圍應用配置器  >  服務和連線的外圍配置器 >  Datebase Engine  > 服務，然後重啟sqlserver服務。（在資料庫非正常關閉之後，可能會出現，“沒法初始化資料來源的錯誤”，此時重啟一下sqlserver服務就可以了）

 

2、然後執行 regedit 進入登錄檔編輯器 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 新建DWORD  (64位值)名為DisallowAdhocAccess 值為0.

 

3、注意：

　　3.1、把需要匯入的Execl檔案關閉;　　

　　3.2、配置工具 > SQL Server 外圍配置管理器 > 功能的外圍應用配置器 > Database Engine > 即席遠端查詢 > 啟用OPENROWSET 和 　　　　　　　　　　             OPENDATASOURCE 支援 (勾選上)

 

3、在執行插入語句前，必須先開啟這個元件服務：

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

 

 　　注意：在每次用完之後，記得把它關閉。

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

 

4、插入語句：

　　
/*新建表y,同時插入資料*/
select * into y from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="G:\1.xls";Extended properties= "Excel 8.0;HDR=YES;IMEX=1"')...[Sheet1$]
/*新建表y,同時插入資料*/

 

/*插入到已有的表x中*/
insert into x select * from  OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=G:\1.xls',Sheet1$);
/*插入到已有的表x中*/

 

引數HDR的值：
HDR=Yes，這代表第一行是標題，不做為資料使用 ，如果用HDR=NO，則表示第一行不是標題，做為資料來使用。系統預設的是YES

 

引數Excel 8.0
對於Excel 97以上版本都用Excel 8.0

IMEX ( IMport EXport mode )設定

　　IMEX 有三種模式，各自引起的讀寫行為也不同，容後再述：

　　0 is Export mode

　　1 is Import mode

　　2 is Linked mode (full update capabilities)

　　我這裡特別要說明的就是 IMEX 引數了，因為不同的模式代表著不同的讀寫行為：

　　當 IMEX=0 時為“匯出模式”，這個模式開啟的 Excel 檔案只能用來做“寫入”用途。

　　當 IMEX=1 時為“匯入模式”，這個模式開啟的 Excel 檔案只能用來做“讀取”用途。

　　當 IMEX=2 時為“連結模式”，這個模式開啟的 Excel 檔案可同時支援“讀取”與“寫入”用途。

意義如下:

0 ---輸出模式;
1---輸入模式;
2----連結模式(完全更新能力)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-672111/，如需轉載，請註明出處，否則將追究法律責任。