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

Question

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
)

Solution 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 SQL Server 2000 (or any other version that I am aware of).

You would need a CLR stored procedure to do this in SQL Server 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.

Solution 2

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.

Comments

[scottm]

I want to just specify the file name.

[scottm]

So, the image/binary fields were only intended to be used with applications that can read the binary data pass that via a query/sp?

[Chip McCormick]

I think the solution below is preferred because it includes a step to convert from a filepath. Tested it with data type image and it worked.

[timmi4sa]

Note: this must have been obvious to all (except me), but the file path seemingly needs to exist on the actual database host. If the file is stored on the remote machine the following error will be encountered: Cannot bulk load because the file ".." could not be opened.

[Mark Whitaker]

+1 Not just "somewhere close" - it works perfectly here (SQL Server 2005).

[Mike Gledhill]

Wow. Stonking tip ! In our company, our in-house data center is a continent away, and we have big latency problems. Getting C# to write a 7Mb file into a SQL Server record takes 30 seconds. But copying the file onto the SQL Server machine, then using this INSERT to write it into a record takes a total of about 3 seconds. Brilliant tip, particularly as it doesn't rely on OLEDB (which always seems to cause us problems with SQL Server)

[Minh Tran]

I encountered Cannot bulk load because the file "[C:\...\file]" could not be opened. Operating system error code 5(Access is denied.). error while executing OPENROWSET. You might get this error if you haven't given your SQL Server instance permission to access the filesystem (specifically permission to access the folder containing C:\Image.jpg) as mentioned here and documented here