Joe Sack: Importing files in SQL Server 2005 using OPENROWSET

From http://www.sqljunkies.com/WebLog/joesack/default.aspx

Importing files in SQL Server 2005 using OPENROWSET
OPENROWSET has new BULK features which I'll definitely be taking advantage of in the future. With OPENROWSET you'll be able to return a result set from a file based on options in a format file (similar to bcp or BULK INSERT), or also import a file in its entirety as a single varbinary(max), varchar(max), or nvarchar(max) data type value.



It is the file import functionality that I'm really looking forward to. This is built-in functionality, so you don't have to depend on external applications to import external files into your SQL Server tables. You use the SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB to tell SQL Server what kind of single-row, single-column data is being read.



For example, in this query I create a new table that will hold product images and their associated descriptions. The ProductIMG column is using the new SQL Server 2005 varbinary(max) data type to hold gif files:



CREATE TABLE dbo.ProductImage

(ProductImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

ProductDESC varchar(255) NOT NULL,

ProductIMG varbinary(max) NOT NULL)

GO



Next, I use OPENROWSET to import a gif file directly into the table from the file system (the BulkColumn is the actual exposed varbinary(max) data being returned):



INSERT dbo.ProductImage

(ProductDESC, ProductIMG)

SELECT 'Used to represent two databases.', -- ProductDESC

BulkColumn -- ProductIMG

FROM OPENROWSET( BULK 'C:\Program Files\Microsoft SQL Server\80\Tools\HTML\dbicon.gif',

SINGLE_BLOB) as ExternalFile



That is all it takes. The file is now stored in the table in a single column of a single row. You could then use SSIS's Export Column task to export the data from these rows into external files again, assuming you added a file path and name column for the transformation to use.

In this example I imported a gif file - but definitely not a fan of serving image files from a database (storing maybe - but not serving). But this functionality definitely opens the door for those of us who don't or can't use third party applications to draw in external files. We can now use Transact-SQL instead.

One uses I may consider is in archiving critical, original legacy data files along with the normalized data. Used within reason of course, this would allow you to maintain the original file alongside the shredded/normalized data in the database so you can troubleshoot data lineage questions and issues.