Insert Picture into SQL Server 2005 Image Field using only SQL

Question

Using SQL Server 2005 and Management Studio how do I insert a picture into an Image type column of a table?

Most importantly how do I verify if it is there?

score 98 · Accepted Answer · edited Apr 27 '11 at 17:42

98

CREATE TABLE Employees
(
    Id int,
    Name varchar(50) not null,
    Photo varbinary(max) not null
)


INSERT INTO Employees (Id, Name, Photo) 
SELECT 10, 'John', BulkColumn 
FROM Openrowset( Bulk 'C:\photo.bmp', Single_Blob) as EmployeePicture

edited Apr 27 '11 at 17:42

Germstorm

9,709
14
67
83

answered Jan 06 '09 at 15:16

Darin Dimitrov

1,023,142
271
3,287
2,928

23

It is important to note that the path listed is looked for on the SQL Server (not the querying machine). So if your SQL Server is not your dev machine you will have to get your image referable from the SQL Server machine. – Vaccano Jun 28 '12 at 21:01
I have same data stored in MSSQL and exported into MySQL? What are steps to display with PHP? – tapaljor May 11 '18 at 04:44

score 41 · Answer 2 · edited Jun 15 '15 at 18:57

41

For updating a record:

 UPDATE Employees SET [Photo] = (SELECT
 MyImage.* from Openrowset(Bulk
 'C:\photo.bmp', Single_Blob) MyImage)
 where Id = 10

Notes:

Make sure to add the 'BULKADMIN' Role Permissions for the login you are using.
Paths are not pointing to your computer when using SQL Server Management Studio. If you start SSMS on your local machine and connect to a SQL Server instance on server X, the file C:\photo.bmp will point to hard drive C: on server X, not your machine!

edited Jun 15 '15 at 18:57

Imran Ali Khan

8,469
16
52
77

answered Mar 23 '10 at 09:15

mathijsuitmegen

2,270
1
34
36

How can I proceed if my image is stored locally and I want to insert into the remote DB? – Jose Luis Apr 16 '15 at 07:36
2

Use a network share: UPDATE Employees SET [Photo] = (SELECT MyImage.* from Openrowset(Bulk '\\your-machine-name\Shared Folder\photo.bmp', Single_Blob) MyImage) where Id = 10 – mathijsuitmegen Apr 16 '15 at 11:13

score 0 · Answer 3 · edited Jun 15 '15 at 18:56

0

Create Table:

Create Table EmployeeProfile ( 
    EmpId int, 
    EmpName varchar(50) not null, 
    EmpPhoto varbinary(max) not null ) 
Go

Insert statement:

Insert EmployeeProfile 
   (EmpId, EmpName, EmpPhoto) 
   Select 1001, 'Vadivel', BulkColumn 
   from Openrowset( Bulk 'C:\Image1.jpg', Single_Blob) as EmployeePicture

This Sql Query Working Fine.

edited Jun 15 '15 at 18:56

Imran Ali Khan

8,469
16
52
77

answered Mar 01 '10 at 09:42

prasanna.yelsangikar

1
1

score 0 · Answer 4 · answered Sep 22 '20 at 12:39

I achieved the goal where I have multiple images to insert in the DB as

INSERT INTO [dbo].[User]
           ([Name]
           ,[Image1]
           ,[Age]
           ,[Image2]
           ,[GroupId]
           ,[GroupName])
           VALUES
           ('Umar'
           , (SELECT BulkColumn 
            FROM Openrowset( Bulk 'path-to-file.jpg', Single_Blob) as Image1)
           ,26
           ,(SELECT BulkColumn 
            FROM Openrowset( Bulk 'path-to-file.jpg', Single_Blob) as Image2)
            ,'Group123'
           ,'GroupABC')

Insert Picture into SQL Server 2005 Image Field using only SQL

4 Answers4

Linked

Related