How to insert picture into Image column?

I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql