Solved

Import and normalize using DTS?

Posted on 2004-04-13
1
310 Views
Last Modified: 2013-11-30
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
Comment
Question by:Tonylmiller
1 Comment
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now