Solved

Import and normalize using DTS?

Posted on 2004-04-13
1
318 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
[X]
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
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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