The BULK Rowset Provider

The BULK Rowset Provider

SQL Server 2005 enhances the OPENROWSET function by providing a BULK rowset provider that can be used to read a file and return the file contents as a rowset, which can then be bulk loaded into a table. You can optionally specify an XML or non-XML format file, using the same format file structure as the bcp.exe utility.

The SINGLE_BLOB option tells the BULK rowset provider to return the contents of a file as a single-row, single-column rowset of type varbinary(max). With the SINGLE_CLOB option, the BULK rowset provider returns the contents of the file as a single-row, single-column rowset of type varchar(max), and with SINGLE_NCLOB it returns a single-row, singlecolumn rowset of type nvarchar(max). SINGLE_NCLOB requires that the input file to be saved in Unicode (widechar) format.

Let's look at an example of BULK rowset provider. Let's assume that the following text is available in a text file called c:\property.txt:

This charming almost new home offers glistening hardwood floors and a formal living and dining room. The Great Room is two story and is basked in sunshine and opens onto the breakfast room and huge kitchen. Upstairs, an outstanding master suite boasts a luxury bath w/ separate shower! Nestled into a cul de sac high on a knoll, this wonderful home is available for immediate occupancy.

Here is how you would bulk load this text into a table, using the OPENROWSET function and the BULK rowset provider:

USE AdventureWorks; GO IF OBJECT_ID('dbo.tblProperty') IS NOT NULL    DROP TABLE dbo.tblProperty; GO CREATE TABLE dbo.tblProperty    (MLSID INT PRIMARY KEY NOT NULL,     Description VARCHAR(max)); GO INSERT INTO dbo.tblProperty       SELECT 12345, txt.*       FROM OPENROWSET(BULK N'C:\Property.txt',                SINGLE_CLOB) AS txt; SELECT * FROM dbo.tblProperty; GO

If you run the preceding script, you should notice that data from the text file is inserted into the Description column.