Sql-server – How to insert binary file data into a binary SQL field using a simple insert statement

sql-serversql-server-2000tsql

I have a SQL Server 2000 with a table containing an image column.

How do I insert the binary data of a file into that column by specifying the path of the file?

CREATE TABLE Files
(
  FileId int,
  FileData image
)

Best Solution

I believe this would be somewhere close.

INSERT INTO Files
(FileId, FileData)
SELECT 1, * FROM OPENROWSET(BULK N'C:\Image.jpg', SINGLE_BLOB) rs

Something to note, the above runs in SQL Server 2005 and SQL Server 2008 with the data type as varbinary(max). It was not tested with image as data type.