Uploading a file to a varbinary on SQL Server

Question

Is there any way to upload a file to a varbinary on SQL Server without writting a program to do it?

Solution 1

Looks like this might work for you... http://www.databasejournal.com/features/mssql/article.php/3632741/Upload-multiple-files-to-VarBinary-column-in-SQL-Server-2005.htm

INSERT INTO Files(File, Name)
SELECT
    * FROM OPENROWSET(BULK 'C:\file.bin', SINGLE_BLOB) AS File
    "file.bin" AS Name
GO

Solution 2

Yes, if you create a "holding" table with just a single varbinary or image column in it, you can use the bcp utility to upload directly into this table. You'll need to know the file size in bytes before you do this, as it's the answer to one of the prompts.

bcp <database_name.schema.table_name> in <your_binary_file> -S server -T

Replace -T with appropriate authentication information if necessary. Then answer the four prompts:

Enter the file storage type of field col [image]:
Enter prefix-length of field col [4]: 0
Enter length of field col [0]: <file_size_in_bytes>
Enter field terminator [none]:

Solution 3

Following Sam's link I've came up with this destiled version:

INSERT INTO Files(File, Name)
SELECT
    * FROM OPENROWSET(BULK 'C:\file.bin', SINGLE_BLOB) AS File
    "file.bin" AS Name
GO

I'm accepting Sam's answer because it sent me in the right direction, this code should be added to that answer.

Comments

[a_hardin]

Thanks to this answer I was able to use bcp to move a varbinary between database servers without having access to the servers' filesystems.