Importing from 1 table into 2 relational tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing from 1 table into 2 relational tables

Author

Topic

chillstroll
Starting Member

4 Posts

Posted - 2002-11-15 : 15:32:15

I need to be able to take data from one table and insert it into 2 relational tables. For example, we have a user table with fname, lname, address, city, state, zip, etc. We now wish to break those out into 2 tables, a user table and an address table, with the address table related to user table by an uid key. Could you give me an example of how to do this with either a stored procedure or DTS package. I'm not sure which to use or if both should be used? Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 16:44:37

If the address table contains a UID which links back to a user, you must require a one-to-one relationship between the users table and the address table. If this is so, I wouldn't bother overnormalizing the data.

However, if each Address has an AddressID and a field in the user table will link to the address, and multiple tables in the database are sharing this address table (i.e., Users and Customers and Contacts, etc), then you could normalize the data, but I still don't know if it is worth it.

Since each user would have probabaly a unique address, and the entire address is dependant on who the user is (and thus the UserID) and no other factors, to me that means it should all be in the same table. (Of course, City and State could be considered dependant on the ZIP code, but that's a whole other issue!)

chillstroll
Starting Member

4 Posts

Posted - 2002-11-15 : 16:58:55

Actually, a user will now be able to have more than one address. For instance a primary address, secondary address, and a shipping address. It's a one to many relationship. Problem is taking data from original table and putting it into new tables with id references. Oh yea, it's about 1.5 million records! So is there a DTS or stored procedure example to get me started. Thanks for the help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 17:05:00

Ok, I got you, now that makes perfect sense. I asume the current addresses you are starting with are all 'Home' addresses.

One thing I would do is create a table of AddressTypes, with a AddressTypeID and then an AddressDescription with values like:

1 Primary
2 Secondary
3 Shipping
..etc...

Now, to move your table to two smaller ones, you just need two append queries:

/* append users to the users table */
INSERT INTO Users (Uid, Fname, LastName)
SELECT
Uid, FName, LastName
FROM
OldTable

/*now, append addresses to the address table.
Assume all addresses are address type 1 = Primary. */

INSERT INTO Addresses (Uid, AddressType, Address1, Address2, City, State, ZIP)
SELECT
Uid, 1, Address1, Address2, City, Start, ZIP
FROM
OldTable

That should do the trick.

chillstroll
Starting Member

4 Posts

Posted - 2002-11-15 : 17:20:35

I don't understand where the UID is coming from on the second insert. Let me explain a little more:

Inserting into the new user table should generate an auto id key. I need to use this key in the address table to link the two records.

For example:
Insert into new_user_table (fname, lname)
Select fname, lname from old_user_table

This should auto generate a uid for me (a primary key set to auto increment)

I need to take that id and run an insert into the new address table.

Something like:
Insert into new_addr_table (uid, addr1, addr2, city, state)
Select new_created_uid_from_new_user_table, old_user_table_addr1, old_user_table_addr2, etc, etc,

Hope this helps explain it, it's about 1.5 million records so i'm assuming this could be time consuming but I don't know. Thanks for the help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-18 : 20:59:41

Ok, I didn't realize the UID would be generated as an autonumber.

The below will work if fname,lastname is a unique key for the "Users" table. If that's the case, after appending the Users tables, the second APPEND query would be:


INSERT INTO Addresses (Uid, AddressType, Address1, Address2, City, State, ZIP) 
SELECT 
Users.Uid, 1, Address1, Address2, City, Start, ZIP 
FROM 
OldTable 
INNER JOIN 
Users
ON
OldTable.Fname = Users.Fname AND
oldtable.LastName = Users.LastName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources