Database Programming: A Brief Note On The Second OPENROWSET Trick

  • Article

Back in March, I presented the "second OPENROWSET trick", a technique for loading a flat file into a single column of a single row of a table.

In researching a question posed by Rob Jarratt of Microsoft Consulting Services in the UK, I discovered that the OPENROWSET extensions that make that trick work were introduced in SQL Server 2005.  Rob was looking for a solution for SQL Server 2000. A little research (I knew I had a reason for leaving SQL Server 2000 installed on my sandbox) led to the following:

CREATE TABLE MyTable

(

    MyText ntext

)

BULK INSERT MyTable

FROM 'C:\Documents and Settings\mystuff\Desktop\DocumentData.xml'

WITH (ROWTERMINATOR = '\0')

If the destination table has more than a single column, a bcp format file can be used to drive the data to the proper column. A citation to the format file would be added to the WITH clause of the BULK INSERT statement. This technique would also work in SQL Server 2005.

Thanks, Rob, for helping me further refine this advice.

     -wp