OPENROWSET関数

MSSQL


SQL Server 2008の機能でプロバイダ情報を指定することで
他のデータベースやAccess、Excelファイルにアクセスしてクエリを発行することができる機能
(アドホッククエリともいう)。



デフォルトではOPENROWSETは無効になっているため以下のSQLで有効にする必要がある

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE 
EXEC sp_configure 'Ad Hoc Distributed Queries', 1 
RECONFIGURE

データを取得するSQLの書式は以下の通り

SELECT * 
FROM OPENROWSET('プロバイダ名', 'プロバイダに応じた接続パス', 'SELECT ステートメント')

他のSQLサーバの情報を取得する例

SELECT * 
FROM OPENROWSET('SQLNCLI10', 'Server=hogeSvr;Trusted_Connection=yes;' , 'SELECT * FROM hogeDb.dbo.tbl_hoge' )

Accessファイルの情報を取得する例

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\hoge.mdb';'admin';'' , 'SELECT * FROM hoge_tbl')

Excelファイル(2003)の情報を取得する例

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\hoge.xls;' , 'SELECT * FROM [Sheet1$] where ROW1=1')

Excelファイルの１行目に見出しを設定していれば上記のようにWhere句を記述することもできる




テキストファイル(csv)の情報を取得する例

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','TEXT;DATABASE=D:\;HDR=NO', 'select * from hoge.csv')

HDRオプションで１行目をヘッダとするか否かを指定する(デフォルトyes)



なお、発行したクエリはSQL Serverのインストールされているサーバで実行されるため
ファイルに対するクエリの場合はファイルをサーバに配置しておく必要がある



動作確認環境:SQL Server 2008 Enterprise Edition