Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Import and normalize using DTS?

Posted on 2004-04-13
1
316 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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how the fundamental information of how to create a table.

809 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