StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If this is a one time (or seldom) thing to do, you can try scripting the data out from the SSMS Wizard as described here:</p> <p><a href="http://sqlblog.com/blogs/eric_johnson/archive/2010/03/08/script-data-in-sql-server-2008.aspx" rel="nofollow">http://sqlblog.com/blogs/eric_johnson/archive/2010/03/08/script-data-in-sql-server-2008.aspx</a></p> <p>Or, if you need to do this frequently and want to automate it, you can try the SQL# library (which I wrote and while most of it is free, the function you need here is not). The function to do this is DB_DumpData and it also generates INSERT statements. The library can be found at: <a href="http://www.SQLsharp.com/" rel="nofollow">http://www.SQLsharp.com/</a>.</p> <p>But again, if this is a one time or infrequent task, then try the data export wizard that is built into Management Studio. That should allow you to then create the SQL script that you can run in Production. I just tested 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 hex string of 6.73 million characters for that one value.</p> <p>EDIT:<br> Another quick and easy way to do this in a manner that would allow you to copy / paste the entire INSERT statement into a SQL script and not have to bother with BCP or SSMS Export Wizard is to just convert the value to XML. First you would CONVERT the VARBINARY to VARCHAR(MAX) using the optional style of "1" which gives you a hex string starting with "0x". Once you have the hex string of the binary data you can concatenate that into an INSERT statement and that entire thing, when converted to XML, can contain the entire VARBINARY field. See the following example:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload