Recommended method to import a .csv file into Microsoft SQL Server 2008 R2?

Question

What is your recommended way to import .csv files into Microsoft SQL Server 2008 R2?

I'd like something fast, as I have a directory with a lot of .csv files (>500MB spread across 500 .csv files).

I'm using SQL Server 2008 R2 on Win 7 x64.

Update: Solution

Here's how I solved the problem the end:

1. 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.
2. Took the advice given on this thread, used bulk insert.
3. 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.
4. 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).
5. I wrote all the code to cycle through files, etc in C#.
6. This method eliminates the biggest bottleneck, which is reading tons of data off the drive and inserting it into the database.

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.

Yes, I know it'd be nicer to have 100% pure C# code to do the job, but in the end:

• (a) For this particular problem, using T-SQL requires much 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.
• (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.

Answer 1

Using BULK INSERT in a T-SQL script seems to be a good solution.

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

You can get the list of files in your directory with xp_cmdshell and the dir command (with a bit of cleanup). In the past, I tried to do something like this with sp_OAMethod and VBScript functions and had to use the dir method because I had trouble getting the list of files with the FSO object.

http://www.sqlusa.com/bestpractices2008/list-files-in-directory/