how to insert image in sql server 2005

Question

how to insert image in sql server 2005

sanjay.dakolia

SSC Enthusiast

Points: 189
More actions
November 5, 2012 at 5:08 am

#269671

hi,
i want to insert image into sql server using sql query. please help.
thanks in advance
sanjay

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

bleroy Hall of Fame Points: 3052 More actions · Answer 1

Hi,

you need to place your question in the SQL Server 2005 forum (http://www.sqlservercentral.com/Forums/Forum338-1.aspx instead of the 2008 forum.

B

deepkt SSC Eights! Points: 951 More actions · Answer 2

Try this,

CREATE TABLE Img(Id INT, Obj VARBINARY(MAX))

INSERT INTO Img (Id ,Obj)

VALUES (1,(SELECT * FROM OPENROWSET(BULK 'c:\windows\Blue Lace 16.bmp', SINGLE_BLOB) Obj))

DROP TABLE Img

Lowell SSC Guru Points: 323504 More actions · Answer 3

you would want to save the filename as well, don't forget....

deepkt (11/5/2012)
Try this,
CREATE TABLE Img(Id INT, Filename varchar(255), Obj VARBINARY(MAX))
INSERT INTO Img (Id ,FileName,Obj)
VALUES (1,'Blue Lace 16.bmp',(SELECT * FROM OPENROWSET(BULK 'c:\windows\Blue Lace 16.bmp', SINGLE_BLOB) Obj))
DROP TABLE Img

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DiverKas SSCrazy Points: 2049 More actions · Answer 4

sanjay.dakolia (11/5/2012)
hi,
i want to insert image into sql server using sql query. please help.
thanks in advance
sanjay

It is generally considered bad practice to store images in the SQL Server DB. Its pretty inefficient. You are better off storing the image in the file system and storing the path in the database.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

DiverKas (11/5/2012)
sanjay.dakolia (11/5/2012)
hi,
i want to insert image into sql server using sql query. please help.
thanks in advance
sanjay
It is generally considered bad practice to store images in the SQL Server DB. Its pretty inefficient. You are better off storing the image in the file system and storing the path in the database.

You might also look into FILESTREAM.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MMartin1 One Orange Chip Points: 27764 More actions · Answer 6

Lowell's solution is a good one. The Image datatype is deprecated thus the need for varbinary(max).

----------------------------------------------------

sanjay.dakolia SSC Enthusiast Points: 189 More actions · Answer 7

hi if i am using this query in a storeprocedure i am getting error. here is the sp and the error

USE [TEMP]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[psp_TEMP]

-- Add the parameters for the stored procedure here

@MimeType varchar(50),

@image image,

@Hid int,

@ImageName varchar(100),

@ImageDesc varchar(100)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

insert into image

(

MimeType,

image

)

select @MimeType,BulkColumn from Openrowset(Bulk @image , Single_Blob) as EmployeePicture

--values

--(

--@MimeType,

--@image

--)

declare @imageId int

set @imageId = @@identity

insert into tables

(

HistoryID,

ImageID,

ImageName,

ImageDescription

)

values

(

@Hid,

@imageId,

@ImageName,

@ImageDesc

)

END

i am getting the below given error :

Msg 102, Level 15, State 1, Procedure csp_Images_History_Insert, Line 20

Incorrect syntax near '@image'.

Lowell SSC Guru Points: 323504 More actions · Answer 8

Sanjay openrowset does not allow variables...it must be a static string inside single quotes. Replace @image with 'c:/filename'

You could build an openrowset as a string and use dynamic sql instead