• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Import and normalize using DTS?

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":

Columns are:
VIN
Color
Make

SQL Tables are:

"Car"
Columns:
CarId
VIN
ColorId
MakeId

"Color"
Columns:
ColorId
ColorName

"Make"
Columns:
MakeId
MakeName


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.

Thanks,

Tony
0
Tonylmiller
Asked:
Tonylmiller
1 Solution
 
arbertCommented:
DTS won't be as easy as you would like it to be for this.  I think I would stage the data and then use TSQL to populate what you need.  Unless you have a lot of manipulation or cleansing to do.  

You can build queries as the input instead of the table and then do distinct selects WHERE NOT EXIST in your existing table.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now