Mega Code Archive
Inserting or Updating an Image File Using OPENROWSET and BULK
<![CDATA[
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>
]]>