| Author |
Topic |
|
marc92
Starting Member
5 Posts |
 Posted - 2009-02-20 : 10:30:28
|
| Here is a sample of data columns I have after importing from several excel files:CompanyName, Unit#, LicensePlate, ModelYear, Make, Model, DateofService, Miles, Hours, ServiceNotes, AirFilter, AirFilter#, OilFilter, OilFilter#, FuelFilter, FuelFilter#I am trying to move about 25k rows into four tables. tblCustomer (company name, plus a few other columns I'll fill later)tblUnits (Unit#, LicensePlate, ModelYear, Make, Model)tblWorkOrders (DateofService, Miles, Hours, ServiceNotes)tblFilterList (AirFilter, AirFilter#, etc)I was planning to use IDENTITY auto-generated numbers for the primary keys in tblUnits, tblWorkOrders, and tblFilterList. UnitID primary key would be the FK in tblWorkOrders; FilterID and CustomerID primary key would be FK in tblUnitsEach row in my imported table represents a single work order, so I will have about 25,000 rows in tblWorkOrders. I only have about 10 customers from these 25k work orders, about 1200 units from the 25k work orders, and about 45 FilterList.I'm still new to SQL so the only way I can see is to move the data into each new table (all 25000 rows), and then try to remove all the duplicates. Surely there is a better way? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-20 : 11:32:11
|
| what represents a duplicate set for you? i.e what all are fields should be considered to look for duplicates? |
 |
|
|
marc92
Starting Member
5 Posts |
Posted - 2009-02-20 : 12:57:11
|
| well, each truck or unit will have multiple work orders, so the unit#, year, make and model will duplicate. There are only about 35 versions of a filter list, so all the filter info will duplicate. Maybe an example would be easier.Here is a typical row of data:HRM, 144, 123-XYZ, 2002, Mack, Mixer, 01/15/2009, 87543, 8432, NULL, EAF5114, 1, LFP3191, 2, LFF8059, 1I have about 25000 of these rows. I have 10 different companies, about 1200 distinct trucks/units, and about 185 2002 Mack Mixers all of which use the same filters.so my company table will have about 10 companies, my units table will have about 1200 units, and my filter table will have about 45 filterlists. Only the work order table will have all 25k rows, as each time work is done on the unit, a work order is generated and added to the table.Does this make sense? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-20 : 13:04:56
|
| so what should be number of records in your table after removing dups? |
|
|
|
|
|
|
Populating multiple tables from a single table