41

I Have a table named FEMALE in my database. It has ID as Primary Key, it has an Image column.

My Question is how do I store an image using a SQL Query?

VINNUSAURUS
  • 1,518
  • 3
  • 18
  • 38

2 Answers2

64

give this a try,

insert into tableName (ImageColumn) 
SELECT BulkColumn 
FROM Openrowset( Bulk 'image..Path..here', Single_Blob) as img

INSERTING

enter image description here

REFRESHING THE TABLE

enter image description here

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 2
    Please explain what is bulkcolumn ? – VINNUSAURUS Mar 27 '13 at 13:59
  • 1
    `bulkcolumn` is part of the syntax. – John Woo Mar 27 '13 at 14:00
  • I Tried this INSERT INTO Female (Image) SELECT BulkColumn FROM Openrowset(BULK 'E:\Vinay\New Folder (2)\ileana00002.jpg ',Single_Blob)as img , but it is giving error Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'ID', table 'PMB.dbo.Female'; column does not allow nulls. INSERT fails. The statement has been terminated. – VINNUSAURUS Mar 27 '13 at 14:10
  • the reason for that is your column `ID` is a *NON_NULLABLE* column. is it an integer column and set as identity? if not, then you need to specifically define column in your `INSERT` statement. – John Woo Mar 27 '13 at 14:13
  • Please show me how to do that iam new to SQL and i need it immediatly. – VINNUSAURUS Mar 27 '13 at 14:17
  • my ID column is PK and incriments 1 on each entry – VINNUSAURUS Mar 27 '13 at 14:18
  • 2
    OOPS THANK YOU it worked :D i did not set identity incriment and seeding property for auto incriment, but it worked thanks for the help :) – VINNUSAURUS Mar 27 '13 at 14:26
  • Can you please show me how to insert other column Details too with the above query, that is with the image how to insert into other rows at the same time ? – VINNUSAURUS Mar 27 '13 at 14:34
  • 1
    [see here](http://books.google.com.ph/books?id=5_AEiJXbyiEC&pg=PA74&lpg=PA74&dq=bulkcolumn+tsql&source=bl&ots=tSBnpEeca8&sig=YiGAw8fX_2Le0yiLAx8Oh_V9z7E&hl=en&sa=X&ei=bgRTUfavGo2ZiAeY84DoDQ&ved=0CDIQ6AEwAQ#v=onepage&q=bulkcolumn%20tsql&f=false) – John Woo Mar 27 '13 at 14:39
  • @JW웃 what is the format its stored in the image column? is it Base64 or any other format? – Ads Jun 26 '13 at 11:23
  • when I use WHERE in the query to insert image to specified rows it does not recognize table column names. why? – Ali.Rashidi Dec 10 '16 at 07:18
  • The path needs to be local to the machine holding SQL Server – dosaki Dec 18 '17 at 15:47
12
Insert Into FEMALE(ID, Image)
Select '1', BulkColumn 
from Openrowset (Bulk 'D:\thepathofimage.jpg', Single_Blob) as Image

You will also need admin rights to run the query.

Ullas
  • 11,450
  • 4
  • 33
  • 50