Sql-server – OPENROWSET, binary files, varchars and varbinaries

sql serveruploadvarbinary

I'm trying to upload a binary file into SQL Server, into a varbinary field. I've came up with this query to do it:

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

but I'm getting this error:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

If I understand correctly, it's reading the file as text (varchar) while it's binary and should be stored as binary on varbinary. I'm afraid it may destroy it in the process, will it? and even if it doesn't, how do I use the CONVERT method?

Best Answer

Putting the data in the same order as the fields made it work (or seem to work at least):

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