title: Create insert statement to insert varbinary blob
body: <p>I want to export some data from my DB and create an Insert-statement to insert those data into another DB with C#.</p>
  
  <p>I made the script for working for nearly everything (and -type) but when I try to use it in one of my tables wich has a field named <code>Contentos</code> which is of <code>type VARBINARY(max)</code> I can't get it to work.</p>
  
  <p>Now I have to select that data, which I realised by using a method which  converts a <code>System.Binary[]</code> (wich I am getting when reading the column) to a hex-string.</p>
  
  <p>But unfortunately I'm failing when it comes to inserting that data...</p>
  
  <p>I'm trying something like this:</p>
  
  <pre><code>INSERT INTO [My.Cool.DBA].[dbo].[Documentos] ([Id], [SomeID], [Name], [Contentos], [LastChange], [LastChangeId]) VALUES ('693c9644-f2b5-4c74-a633-f4942fc1d8e5', 'bca2ac27-71e0-4641-a4b0-3c3e39916e71', 'Whats that', [--&gt; here goes the content &lt;--], '07.08.15 11:18:34:383', '693c9344-f8b5-4d74-a633-f4942fc1d8e5');
  </code></pre>
  
  <p>The content (for example):</p>
  
  <blockquote>
    <p>'504B03041400000008091043E288E9E0D9EC7C1CEB6CF6A7E9290D5A4B04179E2A1B8FE350341542EA67DCCD96DC5E682FD14E7A61E70000002A0300000000'</p>
  </blockquote>
  
  <p>Result: Cannot implicitly convert type varchar to varbinary(max). Use the CONVERT-function</p>
  
  <p>Okay, let's do it!
  The content (for example):</p>
  
  <blockquote>
    <p>CONVERT(varbinary(max), '504B03041400000008091043E288E9E0D9EC7C1CEB6CF6A7E9290D5A4B04179E2A1B8FE350341542EA67DCCD96DC5E682FD14E7A61E70000002A0300000000')</p>
  </blockquote>
  
  <p>Result: </p>
  
  <blockquote>
    <p>Error at converting to <code>uniqueidentifier</code>.</p>
  </blockquote>
  
  <p>The schema is like this:</p>
  
  <ul>
  <li>Id : uniqueidentifier</li>
  <li>SomeID: uniqueidentifier</li>
  <li>Name: nvarchar(max)</li>
  <li>Contentos: varbinary(max)</li>
  <li>LastChange: datetime</li>
  <li>LastChangeId: uniqueidentifier</li>
  </ul>
  
  <p>No matter what I do, this won't work.</p>
  
  <p>So I please would like to know how to</p>
  
  <ol>
  <li>properly read the information from the reader
  ( I'm currently using: ) <code>sb.Append(String.Format("'{0}'", ByteArrayToHexViaLookup32((Byte[])reader[j])));</code></li>
  <li>properly create an insert query wich will insert the data into the db :)</li>
  </ol>
  
  <p>Many thanks in advance!!!</p>
  
  <p>[EDIT]</p>
  
  <p>Here's the thing that finally worked for me:</p>
  
  <p><code>string.Format("CONVERT(VARBINARY(max), 0x{0})", ByteArrayToHexConverter.ByteArrayToHexViaLookup32((byte[])value));</code></p>
  
  <p>The <code>ByteArrayToHexConverter</code> has nothing special, just converting a Bytearray to Hex (as the name says it :b)
  Also I had to let the server convert it back from the hex string to the original <code>byte[]</code></p>
  
  <p><strong>Note</strong>: I need this to be able to execute generated statements out of a text file. So if you don't have this requirements, I suggest you take a look at the answer from <strong>Thomas Levesque</strong></p>
  
  <blockquote>
    <p>You should use a parameterized query:</p>
    
    <p>INSERT INTO [My.Cool.DBA].[dbo].[Documentos] ([Id], [SomeID], [Name],
    [Contentos], [LastChange], [LastChangeId]) VALUES (@Id, @SomeId,
    @Name, @Contentos, @LastChange, @LastChangeId);</p>
  </blockquote>