Professional SQL Server™ 2005 Programming

18.4. OPENROWSET (BULK)

Yet another cousin to BCP, but this one is a far more distant one. You can think of this cousin as being from the side of the family that got most of the money and power (in case you can't tell, I like this one!). OPENROWSET (BULK) marries the bulk rowset provider with the OPENROWSET's ability to be used within queries for fast and relatively flexible access to external files without necessarily needing to load them into an intermediate table.

One of the more common uses for BCP is to load external data files for use by some periodic process. For example, you may receive files that contain things like credit reports, vendor catalogs, and other data that is placed in a generic format by a vendor. This is vital information to you, but you're more interested in a one-time interaction with the data than in truly importing it. OPENROWSET (BULK) allows the possibility of treating that file — or just one portion of that file — as a table. What's more, it can utilize a format file to provide a better translation of the file layout than a simple linked table might provide. The syntax looks like this:

OPENROWSET
( BULK '<data file>' ,
       { [ FORMATFILE = '<format file>' ] [
        [, CODEPAGE [={'ACP'|'OEM'|'RAW'|'<code page>'}]]
        [, FIRSTROW [= <first row>]]
        [, LASTROW [ = <last row no.>]]
        [, MAXERRORS [ = <max errors>]]
        [, ROWS_PER_BATCH [= <rows per batch>]]
        [, ERRORFILE = '<file name>']
 ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

Keep in mind that OPENROWSET is more ...