I want to insert data into sqlserver from access .mdb file, which is the fastest way to perform the data inserts SQL BulkCopy or OPENDATASOURCE using OLEDB.
Data would be like 100,000s of records for about 20 tables.
I am doing this from c# code,where i need which one performs better,I am not able to compare because OPENDATASOURCE is asking for admin permissions.
In my Opinion the very best insert performance for inserting large volumes of data can be attained by using SQL Bulk Copy as this method bypasses the SQL query language entirely and streams data directly to SQL.
Further Performance Improvements
You may read more on Whitepaper: performance of SqlBulkCopy http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf