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?

Whatever else you do, you'll almost certainly have to copy the file to the server, or expose it through a network share from the client machine. Basically, whatever you enter through SQLCMD (except, specifically, for the "GO" command), is sent to the server as a blob of text, and executed there. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 21, 2010 at 10:30

Parfait · Accepted Answer · 2017-11-28 14:40:53Z

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

answered Nov 28, 2017 at 14:40

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

Curious, why the downvote? This has been tested on SQL Server 2017 for Linux.

Nobody · Accepted Answer · 2011-04-20 20:16:17Z

1

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

answered Apr 20, 2011 at 20:16

Nobody

111 bronze badge

Comments

Gart · Accepted Answer · 2010-05-26 16:53:09Z

0

By all means this is not possible with SQLCMD alone.

A more powerful deployment tool is needed here.

answered May 26, 2010 at 16:53

Gart

2,3363 gold badges28 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related