Thursday, August 25, 2011

Inserting an Image file Using OPENROWSET and BULK

In SQL Server 2005 and 2008, UPDATE and OPENROWSET can be used together to import an
image into a table. OPENROWSET can be used to import a file into a single-row, single-column value.

The basic syntax for OPENROWSET as it applies to this recipe is as follows:

OPENROWSET
( BULK 'data_file' ,
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB )


CREATE TABLE dbo.Images
(
 ImgID int NOT NULL,
 Img varbinary(max) NOT NULL
)
GO

Next, a row containing the image file will be inserted into the table:

INSERT dbo.Images
(
ImgID,
Img
)
SELECT 1,
BulkColumn
FROM OPENROWSET(BULK
'Path Of the Image File', SINGLE_BLOB) AS x

This next query selects the row from the table:

SELECT Img
FROM Images
WHERE ImgID = 1



1 comment: