SQL Server 2005/2008: Insert a File in an varbinary(max) column in Transact-SQL

Question

Is it possible to insert a file in a varbinary(max) column in Transact-SQL? If yes, I would be very please to have a code snippet to at least give me an idea how to do that.

Thanks

Solution

It's so easy - once you know it! :-) Found this on Greg Duncan's blog a while ago:

INSERT INTO YourTable(YourVarbinaryColumn)
    SELECT * FROM 
    OPENROWSET(BULK N'(name of your file to import)', SINGLE_BLOB) AS import

And here's the MSDN library documentation on it.

Marc

Comments

[Tony]

remember the path to the file is relative to the SQL server. So if it is C:\test.txt that is on the c: drive of the computer the sql server instance is running on.

[IanGSY]

Note that the AS import at the end is very important!