sql-server-2012ssmsdbforge

How insert Excel file into a varbinary field using SSMS or dbForge Studio?

I have a table in SQL Server with a field that's defined as varbinary. It's going to hold an actual excel (xls) file. Using the SSMS interface (or dbForge Studio interface) how can I insert an actual excel file into an existing record in my table?

Solution

  • Use OPENROWSET Bulk

    INSERT INTO table_name(varbinary_col_name)
SELECT *
FROM OPENROWSET(BULK N'path', SINGLE_BLOB) AS binary_data

    Updating:

    WITH cte(data_blob) AS
(
   SELECT *
   FROM OPENROWSET(BULK N'path', SINGLE_BLOB) AS binary_data
)
UPDATE table_name
SET varbinary_col_name = cte.data_blob
FROM cte
WHERE table_name.id = ?;