How to use insert query to store images


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
  );
GO

We 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 *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;

 

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:

image_tbl

Advertisements

4 thoughts on “How to use insert query to store images

  1. meesha tyagi

    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.

    Like

    1. 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!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s