title: Bulk Insert Sql Server millions of record
body: <p>I have a Windows Service application that receives a stream of data with the following format</p>
  
  <pre><code>IDX|20120512|075659|00000002|3|AALI                 |Astra Agro Lestari Tbk.                                     |0|ORDI_PREOPEN|12  |00000001550.00|00000001291.67|00001574745000|00001574745000|00500|XDS1BXO1|                                        |00001574745000|›§
  IDX|20120512|075659|00000022|3|ALMI                 |Alumindo Light Metal Industry Tbk.                          |0|ORDI        |33  |00000001300.00|00000001300.00|00000308000000|00000308000000|00500|--U3---2|                                        |00000308000000|õÄ
  </code></pre>
  
  <p>This data comes in millions of rows and in sequence <code>00000002....00198562</code> and I have to parse and insert them according to the sequence into a database table. </p>
  
  <p>My question is, what is the best way (the most effective) to insert these data into my database? I have tried to use a simple method as to open a SqlConnection object then generate a string of SQL insert script and then execute the script using SqlCommand object, however this method is taking too long.</p>
  
  <p>I read that I can use Sql BULK INSERT but it has to read from a textfile, is it possible for this scenario to use BULK INSERT? (I have never used it before).</p>
  
  <p>Thank you</p>
  
  <p>update: I'm aware of SqlBulkCopy but it requires me to have DataTable first, is this good for performance? If possible I want to insert directly from my data source to SQL Server without having to use in memory DataTable.</p>