how to insert image into a column

Question

how to insert image into a column

bug

SSC-Addicted

Points: 470
More actions
July 2, 2007 at 9:47 am

#196597

Hi,
How can I insert an image into a table directly? I see this is usually done through application. but I just want to know if it is possible to directly do something like this-
insert into table1 (imgcol) values (somethingbuthow)
Your reply will be highly appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

SSMS does not ahve any way to do it. You could use SSIS or as you said, an app.

There was a little tool shipped with SQL 2000 which did it but I cannot for the life of me remember what it is called. Will look tonight on home machine.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

bug SSC-Addicted Points: 470 More actions · Answer 2

bug

SSC-Addicted

Points: 470

July 2, 2007 at 10:00 am

#716576

Thank you, I will try SSIS...

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

I prefer to store files on the filesystem, and just the location of the file in sql server.

---------------------------------------
elsasoft.org

bledu SSChampion Points: 12335 More actions · Answer 4

CREATE TABLE myTable(Document varbinary(max))
GO

INSERT INTO myTable(Document)    
SELECT * FROM 
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) 
AS Document

GO

Everything you can imagine is real.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

SwePeso

SSC-Dedicated

Points: 39752

July 4, 2007 at 8:26 am

#717180

Nice!

N 56°04'39.16"
E 12°55'05.25"

bug SSC-Addicted Points: 470 More actions · Answer 6

bug

SSC-Addicted

Points: 470

July 5, 2007 at 11:25 am

#717467

thank you so much Bledu!

Crispin Proctor SSCoach Points: 18161 More actions · Answer 7

and there you go... Have to love the simple alternatives.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Takauma Say Hey Kid Points: 697 More actions · Answer 8

Simalar issue:

Having unloaded a table (containing column "Contents long binary") from a Sybase SQLAny db, I am now trying to bulk insert the data into a SS2K5 db table with the same schema ("Contents" data type is now "varbinary(max)").

Here's the SQL:

BULK

INSERT DocumentBinaries from 'C:\TW\KMCA\Scripts\SQL Server\Data\DocumentBinaries_Export.DAT' WITH ( KEEPIDENTITY , FIELDTERMINATOR = '\t' , ROWTERMINATOR ='\n', MAXERRORS = 100 ) ;

DocumentBinaries is the only table with an Image/varbinary column in a group of over 300 being migrated, and the only tbale I can get the above statement to work for.

The following errors result:

Msg 4864, Level 16, State 3, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (contents).

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Takauma

dobiefriendly Mr or Mrs. 500 Points: 583 More actions · Answer 9

Just in case....the SQL 2000 utility was TextCopy.exe

I am trying to insert a wav file as an image which I could do with TextCopy.exe and a stored proc in 2000 but I haven't found a way in 2005 yet.

camino.april SSC Veteran Points: 252 More actions · Answer 10

Hi!

I have an image to insert form a URL path.

I tried to substitute the name of the file, in your example, with the URL "http:\\....." but it is not working.

Could you have any suggestions to help me ?

Thanks,

Antonio

vasanti mestri Right there with Babe Points: 786 More actions · Answer 11

Hi,

i have tried this but i am getting following error:

Cannot bulk load because the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\etwcnf.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.).

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 12

Hello,

Thanks for your post. So - you have the file on the network share and a pointer in the database, but what mechanism in an application controls how the object is accessed? Is there a goto statement or something? Where can I find instructions on this?

Thanks.