CODEWITHSUNDEEP
c#sql
Inside Man
Inside Man

Reputation: 4297

Best Way To Migrating Huge numbers of Data Rows from Access To SQL

I have several Access Database with more than 40000000 rows. I'm reading each row using a Data Reader and insert every row one by one into SQL Database. But it seems it will take weeks and even more!

Is there any way doing this migration faster?

Upvotes: 1

Views: 58

Answers (1)

Milney
Milney

Reputation: 6417

I would recommend exporting your access database to a CSV files (or a number of CSV files), a guide is here: https://support.spatialkey.com/export-data-from-database-to-csv-file/

You can then using Bulk Import or SSIS to import the rows into SQL Server. A reference for this operation would be; 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/

This way should be substantially faster.

An programmatic alternative would be using the SQLBulkCopy class; https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

Upvotes: 2

Related Questions