May 3, 2010 at 8:50 am
Our department just purchased a large database system to replace an internal Access case management database that has been in use for the past 10 years. There are over 20,000 records in it and it crashes on a regular basis. Here's my problem. I've been tasked with migrating all the legacy data out of the Access database into the new SQL Server based system. The vendor has provided an Excel spread sheet with field names and data types and requires that the data conform to their format. The problem I've run into is that the entity relationships in our legacy system don't map to the new system. The data within the tables is mostly the same but the primary key/foreign key relationships are radically different. How on earth am I supposed to get the data into the new format? I don't even know where to start. Any help would be greatly appreciated - tools to use, etc. I don't have the actual schema for the new system, only their spreadsheet. I guess they want all the data dumped into the spreadsheet so they can import it.
May 3, 2010 at 9:10 am
You can approach for SQL Server Integration Services to migrate the legacy data from Access to SQL server.
Abhijit - http://abhijitmore.wordpress.com
May 3, 2010 at 1:06 pm
Without knowing the specifics, I can't get very detailed.
I would be tempted to create all of the Excel layouts as new tables in Access (with PK/FK Referential Integrity applied across the new tables - you'll build your knowledge of the schema as you do this) and then develop a conversion routine. This routine would work along the following lines:
1) Delete all records from the new Excel tables (in appropriate order, so as not to violate your RI)
2) Populate the Excel tables using a mixture of queries and scripts, as needed.
Once you have this routine in place, you can run it as many times as you need until it works. Then you can dump the 'Excel' tables straight out of the database. Or even give them an Access database containing the tables - they should be impressed.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply