How can I create an insert script for a table with a VARBINARY (MAX) field?

Question

I have a table with a VARBINARY (MAX) field (SQL Server 2008 with FILESTREAM)

My requirement is that when I go to deploy in prod, I can provide the IT specialist with only a group of SQL scripts that will be executed in a specific order. The new table that I am doing in production has this field VARBINARY (MAX). Usually with new tables, I exit the CREATE TABLE script script. And if I have data, I need to go with it, then I will exit the script from the INSERT scripts. Not too complicated.

But with VARBINARY (MAX), the stored procedure I used to generate the insert statements in this table. I tried to select this field, print it, copy, convert to hex, etc. The main problem I am facing is that it does not select all the data in the field. I do a DATALENGTH check ([FileColumn]), and if the original row contains the value 1004382, the maximum I can get is the copied or selected data when I reinsert 8000. So basically this is truncated, that is, invalid data .....

How can I do it better? I tried Google like crazy, but I have to miss something. Remember, I cannot access the file system. Must be a script.

Answer 1

Not a script from SSMS

bcp data from / outside or use something like SSMS to generate INSERT statements

Answer 2

If this is a one-time (or rare) thing, you can try to write data from the SSMS wizard, as described here:

http://sqlblog.com/blogs/eric_johnson/archive/2010/03/08/script-data-in-sql-server-2008.aspx

Or, if you need to do this often and want to automate it, you can try the SQL # library (which I wrote, and in most cases it is free, the function that you need here is not). The function for this is DB_DumpData, and also generates INSERT statements. The library can be found at: http://www.SQLsharp.com/ .

But then again, if this is a one-time or infrequent task, try the data export wizard built into Management Studio. This should allow you to create an SQL script that you can run in Production. I just checked this on a table with a VARBINARY (MAX) field containing 3,365,964 bytes of data, and the Generate Scripts wizard generated an INSERT statement with the entire hexadecimal string of 6.73 million characters for this single value.

EDIT:
Another quick and easy way to do this is so that you can copy / paste the entire INSERT statement into the SQL script and not have to worry about the BCP or the SSMS export wizard just need to convert the value to XML. You must first convert VARBINARY to VARCHAR (MAX) using the optional “1” style, which gives you a hexadecimal string starting with “0x”. Once you have a hexadecimal string of binary data, you can combine this into an INSERT statement, and this whole thing, being converted to XML, can contain the entire VARBINARY field. See the following example:

DECLARE @Binary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), REPLICATE( CONVERT(NVARCHAR(MAX), 'test string'), 100000) ) SELECT 'INSERT INTO dbo.TableName (ColumnName) VALUES ('+ CONVERT(VARCHAR(MAX), @Binary, 1) + ')' AS [Insert] FOR XML RAW 

Answer 3

This is more than a little messed up, but in the past and on the Internet I have seen this using a base64 encoded string. You use the xml value to wrap the string, and from there you can convert it to varbinary. Here is an example:
http://blogs.msdn.com/b/sqltips/archive/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa.aspx

I can’t personally talk about how effective or efficient this process is, especially for great values. Since this is an ugly hack at best, I would put it in UDF somewhere, so if a better method is found, you can easily update it.

Answer 4

I have never tried anything like this before, but from the documentation for SQL Server 2008 R2 it seems like using SUBSTRING will work to get the whole varbinary value, although you may have to work with it in chunks using UPDATE with the .WRITE clause to add data.

Updating Large Value Data Types

Use the .WRITE clause (expression, @Offset, @Length) to partially or completely update the varchar (max), nvarchar (max), and varbinary (max) data types. For example, a partial column update varchar (max) can remove or change only the first 200 characters of a column, while a full update will delete or change all data in the column.

For best performance, we recommend inserting or updating data in block sizes that are multiples of 8040 bytes.

Hope this helps.