What is the most efficient way to gt CSV data into a SQL Server Express 2005 Database?
Posted on 2011-05-06
I am using VB 2008 SP1 with ADO.Net 3.5 to try and get the data in 2,600 CSV files (each with approximately 1700 records) into a table in a SQL Server Express 2005 database table.
I tried the first run by reading in a record at a time from the CSV and writing the record to the table.
This would take over 50 hours on an i7 8GB machine.
I have changed the code to read the entire CSV file into a dataset instead of reading it line by line, which should increase the speed somewhat.
But, I am unsure if using a dataset to update the table with all values from a CSV file with a single update call would be faster than using no dataset and using InsertCommand.ExecuteNonQuery for each record.
What is the most efficient (i.e. fastest) way to transfer the data from the CSVs to the SQL Server Express 2005 table using Visual Basic 2008?
I have seen some references to a bulk copy function in sql server, but the data in the CSV files is only a subset of the fields in the table that it is being transferred to. I am not sure if bulk copy can copy where the fields in the CSV files are only a subset of the fields in the SQL table.