SQL Error while Inserting Image...one of those error

When we try to insert image into a sql table, sometime we get error like this.


Msg 491, Level 16, State 1, Line 4
A correlation name must be specified for the bulk rowset in the from clause.

So let's look at a simple SQL statement.

Suppose we have a simple table consisting of ImageID, ImageName, and Image itself column.

Let's create a simple table and we will go through it.

Create Table ImageTable
(ImageID int IDENTITY(1,1),
ImageName varchar(100),
Imagefile IMAGE
)

Let's make sure that we have right table.

SELECT * FROM ImageTable

Now that we have table ready to store or save image, let go ahead and write a sql statement



Insert INTO dbo.ImageTable
(ImageName,Imagefile)
SELECT  'MyPhoto',* FROM OPENROWSET(BULK N'C:\Data\HeaderImage\Header_A.png', SINGLE_BLOB)

You were expecting that this should have insert a row in our table with the image..!!! Right but you got the error.


So before we look into this, let's give a alias name to our sql statement.


Insert INTO dbo.ImageTable
(ImageName,Imagefile)
SELECT  'MyPhoto',* FROM OPENROWSET(BULK N'C:\Data\HeaderImage\Header_A.png', SINGLE_BLOB) A;

Now try to run this script.

It worked!!! Right.

Well lets now try to understand why we got the error in the first place.

Openrowset is looking for an alias in our first sql statement and it cannot find one, so it is throwing an error message "A correlation name must be specified for the bulk rowset in the from clause."

In our case, generally speaking its a good practice to give alias to all our tables. Like

Select * from Customer C;
Select C.CustomerID, C.CustomerFirstName, etc From Customer C.

So here all we have to do is give an alias to openrowset 'A'

:)

Kumar