I have been usually asked the question on how to insert images using insert statements. Here is the way to do that…
Let’s create a table to store images having two columns one using IMAGE data type and other using VARBINARY data type. My intention here is to demonstrate that image can be stored in either of the two types in same manner.
CREATE TABLE image_tbl ( img_varbinary VARBINARY(max), img_image IMAGE ); GOWe shall now use OPENROWSET with BULK mode to read the image data. BULK mode allows us to interpret and read the image data in a binary stream. This is where we take the advantage and insert the data as a binary stream into our table.
In the first example we shall look at queries inserting in column with VARBINARY data type using JPEG and PNG image.
INSERT INTO image_tbl(img_varbinary) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA; INSERT INTO image_tbl(img_varbinary) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;Next, we shall look at queries inserting data in column with IMAGE data type using same JPEG and PNG images.
INSERT INTO image_tbl(img_image) SELECT *INSERT INTO image_tbl(img_image) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;
Here is how the output looks like:
in creating table varbinary(max), here shows the error. The error is this ” right parenthesis missing”. why?.
LikeLike
Hi Divagar, I have tested this code again and it works fine. Can you pls check again?
LikeLike
hey,in my case also insert is not working.It is showing an error near “N’C”. I am executing it in DB Browser for SQLite.
please help.Its urgent.
LikeLike
Hi Meesha, I see. The issue is due to the single quote. When you copy and paste the above code in management studio, the single quotes changes. As a fix just try to delete single quote after “N” and then try to replace with a single quote as we usually do for a string. It should work. Thanks!
LikeLike
in my case there is a column CompName which is primary and allow not null so when I insert image it shows error ……. how to use it now??
LikeLike
Was searching for this code since very long time
Thank you !!!
LikeLike
Thanks Lalit. Glad that it was helpful!
LikeLike
Reblogged this on Interactive Teaching and commented:
mysql> create table p2 (int id , im varbinary(max));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘int i
d , im varbinary(max))’ at line 1
help regarding this
LikeLike
Friend, VARBINARY(MAX) is not supported in MySQL, It’s only supported in MS SQL Server. For MySQL you can use VARBINAERY()data type. Pls check this link MySQL Documentation: https://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html
This statement will work:
create table p2 (id int , im varbinary(10000));
LikeLike
mysql> create table p2 (int id , im varbinary(max));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘int i
d , im varbinary(max))’ at line 1
LikeLike
I have to delete the filepath in the image file before I am able to show it. Is it possible to make an insert without the filepath embeded in the Image file?
LikeLike
Not sure if I understood you correctly… OPENROWSET requires a complete file path to get the image data. Hence it’s not possible to delete filepath. Also file path is not saved in the table, only image data is stored.
LikeLike
You’re a life saver! thank you! Been looking for a simple version of this for ages!
LikeLike
Thanks Mike. Glad that simple trick helped you 🙂
LikeLike
Hi, the code you given is not working for me. please look into matter
Code :
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “C:\User\Comp1\Image1.png” does not exist.
LikeLike
It seems that you are trying to insert image from local machine onto remote instance of sql server. Make sure that “image1.png” exists on the SQL Server machine.
Hope this helps!
LikeLike
I shift that image to database server. Till I have facing same error. Please help.
LikeLike
I see. Please check if your database user has access to the location where image is stored.
LikeLike
I had problem regarding these code
‘right paranthese missing;
LikeLike
Hi Nadeem,
This code is tested and used by many fellow community members. If you have made changes, can you please paste the sample code, I will check and get back to you. Thanks!
LikeLike