You can use a [BCP] utility or create a SSIS package for that purpose. If you need to transform the data in some way, then the SSIS will be the right choice.: http://msdn.microsoft.com/en-us/library/ms162802.aspx
answered Nov 14 '10 at 11:52 PM
I will second Pavel in saying that SSIS is probably the best way to go in general.
With that said, I want to point out that there are many, many ways to import CSV files into SQL Server. You can use bulk insert, BCP, DTS, or opendata source (I wrote an article about that here: http://www.sqlservercentral.com/articles/61552/).
You could also write a custom import routine in another language like C# or Python (http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/). I have found writing custom import scripts in python handy on a couple of occassions, but those involved cases where I needed to do significant preprocessing of the data before it came into SQL Server.
There is another very similar question on this site at [http://ask.sqlservercentral.com/questions/3519/import-data-from-a-csv-file]: http://ask.sqlservercentral.com/questions/3519/import-data-from-a-csv-file
answered Nov 15 '10 at 10:00 AM
Check out Using Import/Export, [Bulk Insert] from Pinal Dave.
If you are dealing with huge data, its better to switch on to SIMPLE RECOVERY MODE to restrict the log growth.
Remember you cannot do more complex operation with any of the methods above, Prefer SSIS when your do complicated data transforms or operations.: 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/
answered Nov 15 '10 at 12:58 AM