title: Recommended method to import a .csv file into Microsoft SQL Server 2008 R2?
body: <p>What is your recommended way to import .csv files into Microsoft SQL Server 2008 R2?</p>
  
  <p>I'd like something fast, as I have a directory with a lot of .csv files (>500MB spread across 500 .csv files).</p>
  
  <p>I'm using SQL Server 2008 R2 on Win 7 x64.</p>
  
  <p><strong>Update: Solution</strong></p>
  
  <p>Here's how I solved the problem the end:</p>
  
  <ol>
  <li>I abandoned trying to use LINQ to Entities to do the job. It works - but it doesn't support bulk insert, so its about 20x slower. Maybe the next version of LINQ to Entities will support this.</li>
  <li>Took the advice given on this thread, used bulk insert.</li>
  <li>I created a T-SQL stored procedure that uses bulk insert. Data goes into a staging table, is normalized then copied into the target tables.</li>
  <li>I mapped the stored procedure into C# using the LINQ to Entities framework (there is a video on www.learnvisualstudio.net showing how to do this).</li>
  <li>I wrote all the code to cycle through files, etc in C#.</li>
  <li>This method eliminates the biggest bottleneck, which is reading tons of data off the drive and inserting it into the database.</li>
  </ol>
  
  <p>The reason why this method is extremely quick at reading .csv files? Microsoft SQL Server gets to import the files directly from the hard drive straight into the database, using its own highly optimized routines. Most of the other C# based solutions require much more code, and some (like LINQ to Entities) end up having to pipe the data slowly into the database via the C#-to-SQL-server link.</p>
  
  <p>Yes, I know it'd be nicer to have 100% pure C# code to do the job, but in the end:</p>
  
  <ul>
  <li>(a) For this particular problem, using T-SQL requires <em>much</em> less code compared to C#, about 1/10th, especially for the logic to denormalize the data from the staging table. This is simpler and more maintainable.</li>
  <li>(b) Using T-SQL means you can take advantage of the native bulk insert procedures, which speeds things up from 20-minute wait to a 30-second pause.</li>
  </ul>