Manually inserting varbinary data into SQL Server

Question

Hi,

We have a SQL Server table for user settings. Originally the settings were domain objects which had been serialized as XML into the table but we recently begun serializing them as binary.

However, as part of our deployment process we statically pre-populate the table with predefined settings for our users. Originally, this was as simple as copying the XML from a customized database and pasting it into an INSERT statement that was ran after the database was built. However, since we've moved to storing the settings as binary data we can't get this to work.

How can we extract binary data from a varbinary column in SQL Server and paste it into a static INSERT script? We only want to use SQL for this, we don't want to use any utilities.

Thanks in advance, Jeremy

Answer 1

I presume you're OK with utilities like Query Analyzer/Mangement Studio?

You can just copy and paste the binary value returned by your select statement (make sure that you are returning sufficient data), and prefix it with "0x" in your script.

Answer 2

You may find it easier to store a template value in a config table somewhere, then read it into a variable and use that variable to fill your inserts:

DECLARE @v varbinary(1000)
SELECT @v = templatesettings from configtable

INSERT INTO usertable VALUES(name, @v, ....)

Answer 3

If I understand you correctly, you want to generate a static script from your data. If so, consider performing a query on the old data that concatenates strings to form the SQL statements you'll want in the script.

First, figure out what you want the scripted result to look like. Note that you'll need to think of the values you're inserting as constants. For example:

INSERT INTO NewTable VALUES 'value1', 'value2'

Now, create a query for the old data that just gets the values you'll want to move, like this:

SELECT value1, value2
FROM OldTable

Finally, update your query's SELECT statement to produce a single concatenated string in the form of the output you previous defined:

SELECT 'INSERT INTO NewTable VALUES ''' + value1 + ''', ''' + value2 + ''''
FROM OldTable

It's a convoluted way to do business, but it gets the job done. You'll need a close attention to detail. It will allow a small (but confusing) query to quickly output very large numbers of static DML statements.