CODEWITHSUNDEEP
sql-serverimageblob
Greg
Greg

Reputation: 11

SQL Server 2008: How to insert one image per each entry in a Table?

In my SQL Server Management Studio 2008 project I have created a table called 

Catalog (
    catalogId NOT NULL PRIMARY KEY, 
    catalogName VARCHAR(20), 
    CatalogImage VARBINARY(MAX)
    )

with 3 entries:

catalogId | catalogName | catalogImage
    1     |     jon     |     NULL
    2     |     jim     |     NULL
    3     |     joe     |     NULL

I wish to add one image per each entry in my table. The following query for inserting an image, for example for jon:

INSERT INTO Catalog (catalogImage)
SELECT BulkColumn FROM OPENROWSET( Bulk 'C:\images\jon.bmp', SINGLE_BLOB) AS BLOB
WHERE catalogId=1

produces the error message "Invalid column name 'catalogId'."

What is the correct query?

Thank you very much in advance, Greg

Upvotes: 1

Views: 12394

Answers (2)

sunn0
sunn0

Reputation: 3046

Since you already have the entries use UPDATE instead of INSERT

UPDATE Catalog SET catalogImage = 
(SELECT BulkColumn FROM OPENROWSET( Bulk 'C:\images\jon.bmp', SINGLE_BLOB) AS BLOB)
WHERE catalogId=1

(untested as well)

Upvotes: 6

drew
drew

Reputation: 1312

Looks like you want to update the rows instead of inserting new ones. Try (untested):

UPDATE Catalog set catalogImage = (SELECT BulkColumn FROM OPENROWSET( Bulk 'C:\images\jon.bmp', SINGLE_BLOB) AS BLOB) WHERE catalogId = 1

Upvotes: 3

Related Questions