使用OPENROWSET把大型檔讀取到SQL Server

引用處
BULK選項被增加到SQL Server 2005中的T-SQL中，它也能夠在SQL Server 2008中使用。當使用SQL Server 2000時，使用sp_OA_Create和sp_OA_Method擴展存儲過程來讀和寫到檔系統中是可能的。XPs還是能夠使用的，但是默認情況下因為安全緣故所以不能使用，當它們可用時，使用更多的安全性能比如OPENROWSET是一個更好的嘗試。

當使用BULK提供者關鍵字時，你可以命名一個資料檔來讀取下面三種類型對象之一：

SINGLE_BLOB，用二進位讀取一個文件(最大值)
SINGLE_CLOB，用varchar讀取一個文件(最大值)
SINGLE_NCLOB，用nvarchar讀取一個文件(最大值)

OPENROWSET返回一個單獨的欄位，命名為BulkColumn，正如它的結果一樣。下面是一個讀取文字檔案的例子。

SELECT BulkColumn FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile

這個關係名，在本例中是MyFile，是由OPENROWSET要求的。
當讀取也必須被發現的單行時，還必須遵守如下所示的要求。

訪問控制一直是備受關注的一個問題。讀取檔的作業系統級別檔的操作是在SQL Server資料引擎使用的帳戶許可權下執行的。因此，只有可以訪問該帳戶的檔才能夠讀取。這包括了網路驅動器或者UNC路徑，如果這個帳號具有該許可權那麼這些是允許的。如果你想讀取網路驅動器，那麼用域用戶來運行SQL Server。

BULK提供者不能在Unicode和普通的ASCII檔之間轉換。它可能會告訴你在檔中使用哪種類型的編碼。如果你沒有，那麼這個結果將會是錯誤4806，可以在下面看到：

SELECT BulkColumn FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_CLOB) MyFile

Msg 4806, Level 16, State 1, Line 1

SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.
Unicode文件必須用下麵顯示的SINGLE_NCLOB選項讀取：

SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_NCLOB) MyFile

類似的是，非文本結構的檔比如Word檔是不能轉化的。在讀取或者作為具有SINGLE_BLOB選項的二進位檔來讀取時，它們必須通過其他的機制來轉化。

當涉及你提供檔的名稱時，OPENROWSET就會顯得不那麼靈活。它必須是一個字串常數。當事先不知道檔案名時，這個要求促進了動態SQL的使用。

下面是一個存儲過程，它可以讀取任何文字檔案並返回一個作為輸出變數的內容：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ns_txt_file_read]
@os_file_name NVARCHAR(256)
,@text_file VARCHAR(MAX) OUTPUT
/* Reads a text file into @text_file
*
* Transactions: may be in a transaction but is not affected
* by the transaction.
*
* Error Handling: Errors are not trapped and are thrown to
* the caller.
*
* Example:

declare @t varchar(max)
exec ns_txt_file_read 'c:\temp\SampleTextDoc.txt', @t output
select @t as [SampleTextDoc.txt]
*
* History:
* WHEN WHO WHAT
* ---------- ---------- ---------------------------------------
* 2007-02-06 anovick Initial coding
**************************************************************/
AS
DECLARE @sql NVARCHAR(MAX)
, @parmsdeclare NVARCHAR(4000)
SET NOCOUNT ON
SET @sql = 'select @text_file=(select * from openrowset (
bulk ''' + @os_file_name + '''
,SINGLE_CLOB) x
)'
SET @parmsdeclare = '@text_file varchar(max) OUTPUT'
EXEC sp_executesql @stmt = @sql
, @params = @parmsdeclare
, @text_file = @text_file OUTPUT
要看看它是如何實現的，只需執行示例腳本：首先創建一個叫做"SampleTextDoc.txt"的文字檔案並把一些文本資料添加到該文件中。在我們的例子中，我們增加了下面的文本"The quick brown fox jumped over the lazy dog."。

DECLARE @t VARCHAR(MAX)
EXEC ns_txt_file_read 'c:\temp\SampleTextDoc.txt', @t output

SELECT @t AS [SampleTextDoc.txt]
結果是：
SampleTextDoc.txt
The quick brown fox jumped over the lazy dog.
(1 row(s) affected)

讀取文字檔案的性能顯著加快了，這是因為這些檔是按順序讀取的。在一個開發機器中使用一個64 bit SQL Server 2008讀取一個750,000,000位元組的文件只需要7秒。