How to script VARBINARY to copy it from one DB to another using a script?

sql-server tsql insert ado.net varbinary
Marc picture Marc · Jan 28, 2011 · Viewed 10.6k times · Source

I need to generate an SQL insert script to copy data from one SQL Server to another. So with .net, I'm reading the data a given SQL Server table and write this to a new text file which can then be executed in order to insert this data on other databases.

One of the columns is a VARBINARY(MAX).
How should and can I transform the obtained byte[] into text for the script so that it can still be inserted on the other databases?

SSMS shows this data as hex string. Is this the format to use? I can get this same format with the following

BitConverter.ToString(<MyByteArray>).Replace("-", "")

But how can this be inserted again?
I tried

CONVERT(VARBINARY(MAX), "0xMyHexString")

This does an insert, but the value is not the same as in the source table.

Answer

Marc picture Marc · Jan 28, 2011

It turned out you can just directly insert the hex string, no need to convert anything:

INSERT TableName (VarBinColumnName) 
VALUES (0xMyHexString)

Just don't ask why I didn't test this directly...

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, but …

Read More
SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …

Read More