Import and normalize using DTS?
Posted on 2004-04-13
I have a Visual FoxPro v.5 (VFP) database which I need to convert into a SQL Server 2000 database. The SQL Server database has primary keys (which are identity columns) on the tables, and it is highly normalized. The VFP database does not have primary identity keys and it is not normalized very much.
DTS looks to have quite a bit of functionality for importing records, and I would like to know if it can do this:
VFP Table "Car":
SQL Tables are:
What I want to do is basically normalize the database as I convert it. For every unique color, I want a record in the "Color" table (but no duplicates), and I want to store the correct ColorId in the "Car" table record in the SQL database, for example.
Can this be done with DTS, and even if it can, is there a better way? I supposed I could use T-SQL to do it, but I'm hoping DTS will be easier. I have about 350 tables in the SQL database.
Please post an example like the one I have above, or point me to a good solution for this if there is a good reference or tutorial somewhere. I am somewhat familiar with DTS already, but I haven't yet seen how to do this.