|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-06 : 23:22:20
|
Doug writes "Windows NT Server 4.0 SP6
SQL 7.0 Latest SP
I have recently normalized a database that holds data for a website that advertises automobiles on the Internet. I am in the process of creating the interfaces that will be used by a team of data entry employees to add data to the tables in the normalized database. My question pertains to inserting data from tables that do not conform to the new normalized database data model. An example would be the best way to explain the problem. The customer uses an auto-industry standard software package that helps the dealership create a database of automobiles currently on their car lot. The application has been used by the customer for years and already contains a large part of the information that will be entered into the new database by hand. The application can create a CSV file containing important auto information that can be easily imported into SQL, reducing the amount of manual data entry. One of the fields in the CSV file lists the automobiles equipment like A/C, power door locks, ABS, 4WD and more. The database that we have created for the customer uses several table to store the same information but in a way that provides scalability. The first table (autos) in the normalized database stores unique properties of an automobile using fields like:
autoid
VIN
stockno
transmission
cylinders
...
Another table (equipment) stores all possible equipment options and all forms of that equipment for scalability and flexibility.
equipmentid
description
abbreviation1
abbreviation2
abbreviation3
...
Here is a sample record from this table:
equipmentid:124
description:Air conditioning
abbreviation1:A/C
abbreviatino2:air cond.
abbreviation3:NULL
...
A link table (auto_equipment) links the auto to it's equipment.
autoid
equipmentid
Sample data:
2 124
2 94
2 22
2 32
...
Now for the problem, how do I programmatically (and automatically) insert an automobile stored in the CSV into the normalized database? (There are more tables in the normalized database that I have not included in the example.) In other words how do I transform this:
"FGSRT152RT3293R32",1998,"Ford","Mustang","GT","A/C,P/D,P/S,P/W,T-Top,Leather"
Into three different tables like this:
AUTO
autoid,vin,stock,transmission,cylinders,...
23,FGSRT152RT3293R32,NULL,A,8...
EQUIPMENT
equipmentid,description,abbreviation1,abbreviation2...
1,air conditioning,A/C,air cond...
2,power door locks,P/D,power locks...
3,power steering,P/S,NULL...
AUTO_EQUIPMENT
23,1
23,2
23,3
...
I appreciate any insight or suggestions you may be able to offer.
Thanks,
Doug" |
|
INSERTing data from a de-normalized database into a normalized database.