How do I INSERT binary data from a local file with SQLCMD?

Question

I need to insert binary data from a file into a varbinary(max) column in SQL Server as a part of my deployment script.

The file is on the local machine, SQL Server is on the remote.

Here is the data table definition

CREATE TABLE [dbo].[Config] (
    [ID]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50)  NOT NULL,
    [Cfg]  VARBINARY(MAX) NOT NULL
);

I need something like this pseudo-command

INSERT INTO Config(ID, Name, Cfg) VALUES (0, 'Default', ????('Default.cfg')??? )

i.e. data is taken from a local file.

How can I do this?

Answer 1

Absolutely you can insert and update binary data into a VARBINARY(MAX) field with sqlcmd by using OPENROWSET() pointing to path of file. Do note the file path cannot be dynamically concatenated or passed as a parameter but hard coded as is.

T-SQL (save as .sql script)

USE mydatabase;
GO

INSERT INTO Config ([ID], [Name], [Cfg]) VALUES (0, 'Default', 
    (SELECT * FROM OPENROWSET(BULK N'C:\Path\To\Default.cfg', SINGLE_BLOB) AS CFG_FILE));

UPDATE Config SET [Cfg] =
    (SELECT * FROM OPENROWSET(BULK N'C:\Path\To\Default.cfg', SINGLE_BLOB) AS CFG_FILE)
WHERE ID = 0;
GO

sqlcmd (command line)

sqlcmd -S myServer\instanceName -i C:\Path\To\myScript.sql