10

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Marc
  • 9,012
  • 13
  • 57
  • 72
  • Is there a reason you don't want to use the Bulk Copy, or SSIS, functions? – Tony Jan 28 '11 at 09:22
  • Yes, I'm including the generated scripts with an application which just runs this script. The application already exists and everything worked fine so far so I don't want to change this. It just doesn't yet work with VARBINARY columns. – Marc Jan 28 '11 at 09:32

2 Answers2

18

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

Marc
  • 9,012
  • 13
  • 57
  • 72
0

There are two questions on SO that may help:

What is the fastest way to get varbinary data from SQL Server into a C# Byte array?

and

How Do I Insert A Byte[] Into an SQL Server VARBINARY column?

Community
  • 1
  • 1
Tony
  • 9,672
  • 3
  • 47
  • 75