Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to insert image in sql server 2005 Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 5:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 5, 2012 5:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
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
Posted Monday, November 5, 2012 6:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:20 PM
Points: 219, Visits: 338
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
Posted Monday, November 5, 2012 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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
Posted Monday, November 5, 2012 8:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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
Posted Monday, November 5, 2012 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381137
Posted Tuesday, November 6, 2012 12:02 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
Lowell's solution is a good one. The Image datatype is deprecated thus the need for varbinary(max).
Post #1381682
Posted Saturday, November 10, 2012 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, November 11, 2012 4:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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
Posted Monday, November 12, 2012 7:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1383678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse