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

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')??? )

开发者_StackOverflow中文版i.e. data is taken from a local file.

How can I do this?

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

Google 'SQL BULK INSERT' that can get you at least some insertion from files, though it is meant for multiple rows.

By all means this is not possible with SQLCMD alone.

A more powerful deployment tool is needed here.