How to insert JPEG into a SQL Server 2000 database field of image type using Transact SQL

Question

Hi,

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.

Answer 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:

CREATE TABLE MyTable 
(
    ID INT PRIMARY KEY IDENTITY (1,1), 
    ImageColumnName IMAGE NULL
)
GO

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

-- get a pointer value to the row+column you want to 
-- write the image to
DECLARE @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'

Answer 2

Hi

Here is a sample to check out http://www.java2s.com/Code/SQLServer/Insert-Delete-Update/InsertingImages.htm

cheers

Answer 3

poor soluion.this only inserts the path,not any file