Search code examples
sql-servert-sqlinsertado.netvarbinary

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

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.

Solution

  • 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...