Making MS SQL DB from several ACCESS DB Tables.
I am making an application in which I plan to combine tables from several access databases into one MS SQL SERVER Database. My design ideas is following:
I am using C# and Visual Studio:
a. List the name of access db.
b. Connect with access db using oledb connection
b. For each access db, select table
c. Read each row
d. Connect with MS SQL Server database
e. Insert that row to database
Somehow, between step c and e, I need to insert one column to distinguish records' the belonged to different access data sets.
I am talking about a large number of rows (> million and multiple tables and multiple datasets). I assume my approach is going to be time consumting. I needed your opinion in an efficient approach to what I have listed here.
Thanks in advance for your help.