Invalid column name

Using SQL Server 2014

I have created a test database for testing where I wish to store a images for various departments.  When running a query to insert the image I get the Invalid column name error.  

The error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'AgencyID'.

Database Name: ImageTest
Table Name: Agency
Fields AgencyID
Field: ImageName
Field: Image

USE ImageTest

INSERT INTO Agency ([ImageName], [Image])
SELECT 'Glencoe', *
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image
where AgencyID = 1;

The way I want to insert the image is based on the AgencyID field.  So I can insert multiple images into the table based on the AgencyID Value.    That way I can pull them into a Crystal Report based on the AgencyID Field when the report is run.  

I have attached  the script to build the database and tables.
Who is Participating?
vastoConnect With a Mentor Commented:
"where AgencyID = 1;"

the script contains just the commands which will create the database . What is the structure of table Agency ?
If you have AgencyID then the insert should look like this
INSERT INTO Agency ([AgencyID],[ImageName], [Image])
SELECT 1, 'Glencoe', image.*
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image

Open in new window

Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
A where condition on an insert makes no sense, insert creates new records, you specify all data going into a new record, so there is no sense in checking whether some field has some value, you specify all the values.

WHERE would only make sense in an UPDATE statement, but then you would set all records with agencyid=1 to one and the smae picture. An inset just specifies new data, if the new data comes from a source table, you may apply a WHERE clause to that, but then the WHERE clause is part of the SELECT query on that source data, to limit which records you copy. WHERE always filters data, it doesn't set or specify data.

Bye, Olaf.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
>INSERT INTO Agency ([ImageName], [Image]) SELECT 'Glencoe', * ...

* needs to be specified. * means All Fields
Olaf DoschkeSoftware DeveloperCommented:
* is not the problem, that is - as vasto put it more clearly - just the one blob field in this case image.*, since it has no name from the OPENRWOSET, it's a convenient wa to specify it.

The problem is simply, that the where clause is not about the image nor any other table, it's about a field of the table you insert into, and since you add new data to that target table there is no sense in applying a filter WHERE clause to that target tables column.

Bye, Olaf.
MikeM670Author Commented:
It looks like I was not really clear in my questions initially but I was able to figure out how to do it with all your help.  

The example vasto gave that worked inserted a new row with the agencyid value of 1.  I already had a agencyid of 1 (in fact the table I had was agencies 1-4 in it with the rest of the table fields empty.  I wanted to add the image to the existing agencyid that was equal to 1.  

I redid the table Agency and added a primary key to the AgencyID Field so I would not have duplicate AgencyID's in the table and also added a ImageURL field to try the other method of image storage/retrieval.

The query I ended up using was this one.

USE ImageTest

INSERT INTO Agency ([AgencyID],[Name],[ImageName],[Image],[ImageURL])
SELECT 1, 'Glencoe', 'Glencoe', image.*,''
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image

I thank both of you for the help on solving this.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.