Manually inserting varbinary data into SQL Server

Question

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

Thanks for the all of the responses, but we've decided to simply depoy the binary data as a .dat file using the SQL bulk copy utility. — Jeremy Jarrell
– Jeremy Jarrell, Commented Jun 30, 2009 at 12:22

Jeremy Smyth · Accepted Answer · 2009-06-29 14:33:29Z

4

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

answered Jun 29, 2009 at 14:33

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

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

Comments

Rory · Accepted Answer · 2012-12-11 18:53:20Z

3

From SQL Server 2008 onwards you can use Tasks > Generate Scripts and choose to include data. That gives you INSERT statements for all rows in a table which you can modify as needed.

Here's the steps for SQL 2008. Note that the "Script Data" option in SQL 2008 R2 is called "Types of data to script" instead of "Script Data".

answered Dec 11, 2012 at 18:53

Rory

42.2k56 gold badges191 silver badges271 bronze badges

1 Comment

Jurijs Kastanovs Over a year ago

This is the best answer. I had problems that if I did it manually, after insert, I found that there were some extra symbols inserted in my varbinary column for some reason. For example 0xFblablabla turned into 0x0Fblablabla. This fixed this! I would just add that this link explains how to enable Data insert script generation: dzone.com/articles/…

David M · Accepted Answer · 2009-06-29 14:31:46Z

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.

answered Jun 29, 2009 at 14:31

David M

73.2k13 gold badges164 silver badges187 bronze badges

2 Comments

Jeremy Jarrell Over a year ago

Hi David, Thanks for the suggestion. We actually tried that but we kept getting additional trailing 0's inserted in the string. This caused the resulting object that we tried to deserialize to be invalid.

David M Over a year ago

Are you sure it is a varbinary and not a binary column then?

Community · Accepted Answer · 2017-05-23 11:51:34Z

1

David M's suggestion of using the 0x prefixing works but i had to add an extra 0 at the end of varbinary data that i was trying to insert.

See the stackoverflow entry below to see the issue with additional 0 that gets added when converting to varbinary or saving to varbinary column

Insert hex string value to sql server image field is appending extra 0

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Feb 7, 2014 at 0:26

Lin

66311 silver badges27 bronze badges

Comments

SurroundedByFish · Accepted Answer · 2009-06-29 17:27:05Z

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.

Collectives™ on Stack Overflow

Manually inserting varbinary data into SQL Server

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related