October 9, 2002 at 6:23 am
I have a database with unnormalized tables. I have created a new database with the original tables normalized into more tables. The column names and data types are still the same but I have added Identity columns which are used as keys to cross reference the tables. I have managed to export columns separately using DTS and the ID column increments automatically. The problem is that the columns are copied exactly and therefore the data gets repeated with the only difference between records being the ID column. For instance, one table has a ID column, a month column, and a year column. In the old database, the month and year columns were jammed in with everything else in one table, so it would go:
DATE_ID MONTH YEAR
1 3 2002
2 3 2002
3 3 2002
4 4 2002
5 4 2002
....
how do I get it not to duplicate the data? Also, the foreign keys in the new tables need to be synchronized.
Another problem that I have is one table contains longitude and latitude coordinates. The datatype in the old database is numeric and so is the new database, but when I use DTS to import the data, instead of showing 5 decimal places, it doesn't show any decimals. This table also has duplicate entries which I have to get rid of.
I can't change any data types since the data is always going to come in in the old format, and I have to write a DTS package that will handle the redistribution of the data every time the data comes in.
Any help would really be appreciated.
Andrea
Andrea
October 9, 2002 at 7:28 am
I'm not entirely sure how you are setting up the DTS package, but I don't think you can do what you want in a simple step.
First of all, you will have to fill up all the leaf tables (which do not contain any foreign keys), using a separate task. In the step, you should use a query to specify the source. In the example for the dates, it would read 'SELECT MONTH, YEAR FROM SOURCE_TABLE GROUP BY MONTH, YEAR'.
Once you're finished importing all 'leaf' tables, you can start moving up the 'tree'. This is a lot trickier, since you will have to fill up all the foreign key fields with the proper ID's. You can do this using a query, joining the source table with the newly created destination tables, or by writing a stored procedure (or ActiveX script task) that loops through each source record and searches for the accompanying ids. The first option (using the join) is probably faster.
Good luck...
October 10, 2002 at 1:19 am
OK, but first I have to solve the first problem.
I can't just fill the tables, i need a trigger or something to delete duplicate records. This is not a once off copying of data, this has to happen once a month when the data comes in because it will always come in in the two old tables.
Before you think I'm really stupid please read my bio 🙂
Andrea
Andrea
October 10, 2002 at 11:04 am
Hi Andrea...
Later today I will find and post an example of a query I use that might help point you in the right direction...your question deals as much with sql as dts...have you tried posting to the sql forum here or to something like aspsqlhowto@aspfriends.com?
hth,
Michael
Michael Weiss
Michael Weiss
October 10, 2002 at 11:40 am
Thanx guys,
I have managed to sort out the duplication problem by using SELECT DISTINCT in my dts packages, now I just need to find out how to synchronize the PK's with the corresponding FK's in the new tables.
Andrea
Andrea
October 10, 2002 at 11:46 am
I always use stored procs for things like this. It may be old school, but it never fails me.....and much more cross platform compatible, too.....
October 10, 2002 at 11:56 am
I guess I am lost here, Andrea...if you join your tables with foreign key constraints then the primary keys will by definition be synchronized with the foreign keys...perhaps a breakdown of your table structure would help us see exactly what you are trying to accomplish...
Michael
Michael Weiss
Michael Weiss
October 10, 2002 at 10:54 pm
Andrea,
This is the query example I was speaking of...basically, it adds records to a table (cust_loc) that links a customers table to a customer_location table (many to many). It only adds those records that match the records in a staging table (this would be representative of your flat file, ie new records) and are NOT already in the cust_loc table.
INSERT INTO cust_locations(cust_id, location_id)
SELECT c.cust_id, t.loc_id
FROM customer c
JOIN staging_table s ON c.cust_name = s.customername
JOIN locations t ON s.customerlocation = t.location_nm
WHERE NOT EXISTS
(SELECT null FROM cust_locations cl
WHERE cl.cust_id = c.cust_id and cl.location_id = t.loc_id)
Tables:
Staging -
Customer
CustomerLocation
Product
SaleAmt
Customer -
cust_id
customer_nm
Locations -
loc_id
location_nm
cust_locations -
cust_id
location_id
Anyway, I realize you have your import problem handled but thought this would give you an idea of another approach...
hth,
Michael
Michael Weiss
Michael Weiss
October 11, 2002 at 6:34 am
Thanx again for the replies.
The problem with the foreign keys is this:
Data comes in once a month (about 20 000 records) into the old tables from another company. I have added PK's to the new tables and made a dts package to import the data. The PK's get put in automatically when the package executes. And by using SELECT DISTINCT on the columns that I am copying to their respective new tables, I have eliminated the duplication problem. I'm not sure yet how I'm gonna handle this next month, but thats a problem for next month when I hopefully know more about SQL 🙂
The problem is that I don't know how to reference the keys properly in foreign tables. For example, if the old table looked like this:
OLDTABLE
Order, Product, Customer, Date
and the new tables looked like this:
ORDERS
OrderID, ProductID, CustomerID, Date
CUSTOMERS
CustomerID, Firstname, Lastname, etc....
etc...
How do I get the correct CustomerID to go to the correct record in the Orders table (and all the other tables referencing each other)?
Andrea
Andrea
October 11, 2002 at 6:34 am
Thanx again for the replies.
The problem with the foreign keys is this:
Data comes in once a month (about 20 000 records) into the old tables from another company. I have added PK's to the new tables and made a dts package to import the data. The PK's get put in automatically when the package executes. And by using SELECT DISTINCT on the columns that I am copying to their respective new tables, I have eliminated the duplication problem. I'm not sure yet how I'm gonna handle this next month, but thats a problem for next month when I hopefully know more about SQL 🙂
The problem is that I don't know how to reference the keys properly in foreign tables. For example, if the old table looked like this:
OLDTABLE
Order, Product, Customer, Date
and the new tables looked like this:
ORDERS
OrderID, ProductID, CustomerID, Date
CUSTOMERS
CustomerID, Firstname, Lastname, etc....
etc...
How do I get the correct CustomerID to go to the correct record in the Orders table (and all the other tables referencing each other)?
Andrea
Andrea
October 11, 2002 at 10:25 am
Andrea,
If you look at the query example I posted, I think you will see that it is almost exactly what you need...and can be modified to do what you need done. Think of your old table as the staging table...insert into your customers table only customers that do not already exist there...same with products, etc. Your order table is going to be like the cust_loc table in the query example...you pull from orders the order id, get the cust id from the cust table by matching (joining) the orders table to your customer table on customer name (since your order table does not have cust id in it). In a separate query, do the same with products, and so on until you have the data loaded. If you need more explanation or help, just ask! These queries can be confusing and intimidating until you get used to dealing with them...hang in there though...you will get it!
hth,
Michael
Michael Weiss
Michael Weiss
October 14, 2002 at 8:45 am
Thank you so much for all your help Michael 🙂
I will try your query as soon as I can figure it out - you are right about it being confusing in the beginning! I can't wait till its not the beginning for me anymore.
I really appreciate your help on this! I never expected anyone to be so patient when I posted this problem 🙂
Andrea
Andrea
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply