how to insert image in sql server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » how to insert image in sql server 2005

how to insert image in sql server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

sanjay.dakolia

Posted Monday, November 05, 2012 5:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17

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

Post #1381001

bleroy

Posted Monday, November 05, 2012 5:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:46 AM
Points: 138, Visits: 549

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

Post #1381010

deepkt

Posted Monday, November 05, 2012 6:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:42 AM
Points: 202, Visits: 286

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

Post #1381054

Lowell

Posted Monday, November 05, 2012 6:52 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 11,648, Visits: 27,760

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1381055

DiverKas

Posted Monday, November 05, 2012 8:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

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.

Post #1381119

Sean Lange

Posted Monday, November 05, 2012 8:54 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1381137

mmartin1

Posted Tuesday, November 06, 2012 12:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 194, Visits: 643

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

Post #1381682

sanjay.dakolia

Posted Saturday, November 10, 2012 5:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17

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'.

Post #1383360

Lowell

Posted Sunday, November 11, 2012 4:45 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 11,648, Visits: 27,760

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

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1383429

Sean Lange

Posted Monday, November 12, 2012 7:01 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

And don't forget as previously mentioned that the image datatype is deprecated. Instead use varbinary(max). It will behave the same way but is the newer preferred datatype for this.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1383678

« Prev Topic | Next Topic »

Permissions