I am converting a huge DBase db into SQL. What I have done is extracted data from DBase into non-delimited files and BCPed the data into a SQL db - HMS(Note that this db has the same table structure as the one in DBase). I have a db in SQL - RxOne where some of the table structures match with the DBase db. The aim is to get the data from HMS to RxOne(both the dbs are on the same server) in the right order i.e maintaining the FK relationships etc. So here is what I am doing.
1. Read data from HMS using Stored Procedures
2. Map the fields in VB
3. Insert data into RxOne using Stored Procedures
Now to do this for 20000 records it takes about 70 minutes. The other thing I have tried is export into a tab delimited file so that I can BCP from there, this takes about 60 mins.
I have a few million records to convert, does anyone know how to speed up this process?