title: How can I insert 10 million records in the shortest time possible?
body: <p>I have a file (which has 10 million records) like below:</p>
  
  <pre class="lang-none prettyprint-override"><code>    line1
      line2
      line3
      line4
     .......
      ......
      10 million lines
  </code></pre>
  
  <p>So basically I want to insert 10 million records into the database.
  so I read the file and upload it to SQL Server.</p>
  
  <p>C# code</p>
  
  <pre class="lang-cs prettyprint-override"><code>System.IO.StreamReader file = 
      new System.IO.StreamReader(@"c:\test.txt");
  while((line = file.ReadLine()) != null)
  {
      // insertion code goes here
      //DAL.ExecuteSql("insert into table1 values("+line+")");
  }
  
  file.Close();
  </code></pre>
  
  <p>but insertion will take a long time.
  How can I insert 10 million records in the shortest time possible using C#?</p>
  
  <p><strong>Update 1:</strong><br>
  Bulk INSERT:</p>
  
  <pre class="lang-sql prettyprint-override"><code>BULK INSERT DBNAME.dbo.DATAs
  FROM 'F:\dt10000000\dt10000000.txt'
  WITH
  (
  
       ROWTERMINATOR =' \n'
    );
  </code></pre>
  
  <p>My Table is like below:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DATAs
  (
       DatasField VARCHAR(MAX)
  )
  </code></pre>
  
  <p>but I am getting following error:</p>
  
  <blockquote>
    <p>Msg 4866, Level 16, State 1, Line 1<br>
    The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.  </p>
    
    <p>Msg 7399, Level 16, State 1, Line 1<br>
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.</p>
    
    <p>Msg 7330, Level 16, State 2, Line 1<br>
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".</p>
  </blockquote>
  
  <p>Below code worked:</p>
  
  <pre class="lang-sql prettyprint-override"><code>BULK INSERT DBNAME.dbo.DATAs
  FROM 'F:\dt10000000\dt10000000.txt'
  WITH
  (
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '\n'
  );
  </code></pre>