How To Insert Jpeg Into A Sql Server 2000 Database Field Of Image Type Using Transact Sql

I'm trying to figure out how to insert a .JPG file into a SQL Server 2000 database field of type image using Transact SQL. Thanks.

Solution 1:

Use OPENROWSET:

INSERT MyTable (ImageColumnName) 
SELECT BulkColumn FROM OPENROWSET (BULK 'c:\myjpeg.jpg', SINGLE_BLOB) AS X

EDITED Whoops, you're using 2000--the previous solution is not supported. You have to use WRITETEXT:

CREATETABLE MyTable 
(
    ID INTPRIMARY KEY IDENTITY (1,1), 
    ImageColumnName IMAGE NULL
)
GO

-- must insert a dummy value into the image column for TEXTPTR -- to work in next bitDECLARE@RowIdINTINSERT MyTable (ImageColumnName) VALUES (0xFFFFFFFF)
SELECT@RowId= SCOPE_IDENTITY()

-- get a pointer value to the row+column you want to -- write the image toDECLARE@Pointer_Value varbinary(16)
SELECT@Pointer_Value = TEXTPTR(ImageColumnName)
FROM MyTable
WHERE Id =@RowId-- write the image to the row+column pointer
WRITETEXT MyTable.ImageColumnName @Pointer_Value 'c:\myjpeg.jpg'

Solution 2:

There is a tool called textcopy.exe You can find it under MSSQL\Binn or get it with SQL Server 2000 SP4

Alexander Chigrik wrote a nice stored procedure for usinig it with SQL query:

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

Solution 3:

The stored procedure found in this tutorial worked for me:

Brief tutorial on text, ntext, and image