title: Convert a file full of "INSERT INTO xxx VALUES" in to something Bulk Insert can parse
body: <p>This is a followup to my first question "<a href="https://stackoverflow.com/questions/9982577/">Porting “SQL” export to T-SQL</a>". </p>
  
  <p>I am working with a 3rd party program that I have no control over and I can not change. This program will export it's internal database in to a set of <code>.sql</code> each one with a format of:</p>
  
  <pre><code>INSERT INTO [ExampleDB] ( [IntField] , [VarcharField], [BinaryField])
  VALUES
  (1 , 'Some Text' , 0x123456),
  (2 , 'B' , NULL),
  --(SNIP, it does this for 1000 records)
  (999, 'E' , null);
  (1000 , 'F' , null);
  
  INSERT INTO [ExampleDB] ( [IntField] ,  [VarcharField] , BinaryField)
  VALUES
  (1001 , 'asdg', null),
  (1002 , 'asdf' , 0xdeadbeef),
  (1003 , 'dfghdfhg' , null),
  (1004 , 'sfdhsdhdshd' , null),
  --(SNIP 1000 more lines)
  </code></pre>
  
  <p>This pattern continues till the <code>.sql</code> file has reached a file size set during the export, the export files are grouped by <code>EXPORT_PATH\%Table_Name%\Export#.sql</code> Where the # is a counter starting at 1.</p>
  
  <p>Currently I have about 1.3GB data and I have it exporting in 1MB chunks (1407 files across 26 tables, All but 5 tables only have one file, the largest table has 207 files).</p>
  
  <p>Right now I just have a simple C# program that reads each file in to ram then calls <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx" rel="nofollow noreferrer">ExecuteNonQuery</a>. The issue is I am averaging 60 sec/file which means it will take about 23 hrs for it to do the entire export.</p>
  
  <p>I assume if I some how could format the files to be loaded with a BULK INSERT instead of a INSERT INTO it could go much faster. Is there any easy way to do this or do I have to write some kind of Find &amp; Replace and keep my fingers crossed that it does not fail on some corner case and blow up my data.</p>
  
  <p>Any other suggestions on how to speed up the insert into would also be appreciated.</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I ended up going with the <a href="https://stackoverflow.com/questions/10013197">parse and do a SqlBulkCopy method</a>. It went from 1 file/min. to 1 file/sec.</p>