How can I insert binary file data into a binary SQL field using a simple insert statement?

Question

I have a MSSQL 2000 server with a table containing an image field. How do I insert the binary data of a file into that field by specifying the path of the file?

CREATE TABLE Files
(
  FileId int,
  FileData image
)

Answer 1

If you mean using a literal, you simply have to create a binary string:

insert into Files (FileId, FileData) values (1, '0x010203040506')

And you will have a record with a six byte value for the FileData field.

---

You indicate in the comments that you want to just specify the file name, which you can't do with MSSQL 2000 (or any other version that I am aware of).

You would need a CLR stored procedure to do this in MSSQL 2005/2008 or an extended stored procedure (but I'd avoid that at all costs unless you have to) which takes the filename and then inserts the data (or returns the byte string, but that can possibly be quite long).

---

In regards to the question of only being able to get data from a SP/query, I would say the answer is yes, because if you give SQL Server the ability to read files from the file system, what do you do when you aren't connected through Windows Authentication, what user is used to determine the rights? If you are running the service as an admin (God forbid) then you can have an elevation of rights which shouldn't be allowed.

Answer 2

Unfortunatelly, you cannot insert the file into table from file using T-SQL only. You need to use client application to read the file to array of bytes and then pass this array to stored procedure as SqlDbType.Image parameter.