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.
Add a comment
|
3 Answers
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'
1 Comment
Kate
that's what i tried doing but i think it's sql server 2005 syntax, doesn't seem to work in 2000
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:
Comments
The stored procedure found in this tutorial worked for me:
