Insert Picture into SQL Server 2005 Image Field using only SQL
SqlSql ServerSql Server-2005ImageSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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!
Solution 3 - Sql
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.
Solution 4 - Sql
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')