Inserting or Updating an Image File Using OPENROWSET and BULK : OPENROWSET « Data Types « SQL Server / T-SQL Tutorial





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.

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


Parameter      Description
data_file      The name and path of the file to read.
SINGLE_BLOB |      Designate the SINGLE_BLOB object for importing into a varbinary(max) data
SINGLE_CLOB |      type, SINGLE_CLOB for ASCII data into a varchar(max) data type, and
SINGLE_NCLOB      SINGLE_NCLOB for importing into a nvarchar(max) UNICODE data type.

Referenced from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
20>
21>
22> CREATE TABLE ImageTable(
23> ID int NOT NULL,
24> Gif varbinary(max) NOT NULL
25> )
26> GO
1>
2> INSERT ImageTable
3> (ID, Gif)
4> SELECT 1,
5> BulkColumn
6> FROM OPENROWSET(BULK 'C:\yourImage.gif',SINGLE_BLOB) AS x
7> GO
Msg 4860, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Cannot bulk load. The file "C:\yourImage.gif" does not exist.
1>
2> SELECT Gif
3> FROM ImageTable
4> WHERE ID = 1
5> GO
Msg 208, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Invalid object name 'ImageTable'.
1>
2> UPDATE ImageTable
3> SET Gif =
4> (SELECT BulkColumn
5> FROM OPENROWSET(BULK
6> 'C:\newImage.gif',
7> SINGLE_BLOB) AS x)
8> WHERE ID =1
9> GO
Msg 208, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Invalid object name 'ImageTable'.
1>
2> drop table ImageTable
3> GO
Msg 3701, Level 11, State 5, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Cannot drop the table 'ImageTable', because it does not exist or you do not have permission.
1>










5.18.OPENROWSET
5.18.1.Inserting or Updating an Image File Using OPENROWSET and BULK
5.18.2.OPENROWSET(BULK 'C:\ER.vdx',SINGLE_BLOB)