Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import and normalize using DTS?

Posted on 2004-04-13
1
Medium Priority
?
325 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 500 total points
ID: 10813943
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

783 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