Alter several tables at once

[jw5107]

I have a d/base that houses 18 table. This d/base is something that our IT folks control and update. Every table has a common field named: IID. The problem is - none of these tables have a primary key or even an IID-ID field that will allow me to join all the tables - to allow this tool to run smoother.

There is one MAIN table. What I want to do is create an autonumber field (named: KeyID) in the main table, make it the primary key, add a column to ALL the other tables, that shows the KeyID value where the IID field is equal.

Is the possible with ALTER TABLE, OR INSERT INTO???
Any examples or suggestions??

I'm afraid that onces all the tables are joined, it will cause problems because the tables are updated every night on the network/server. Is there a way to do this everyday (alter the tables) without breaking the relationships??
Our data is very DE-normalized and I am tryin' to find a way to make it all run smoother....

Thanks in advance!!
jw

 

[KenReay]

Hi

You could quite readily write a series of SQL statements to add an autonumber column to one table, and a column of data type long to the remaining tables. The autonumber column will automatically populate with values when you add it. But you will need to pupulate the (long type) linking column in the child table(s). How are you going to identify which child record(s) belong to each parent, in order to facilitate your links, if you have no primary key(s)?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jw5107]

KenReay,

Thanks for the quick response!!
I think I am headed in a different path now....
Let me explain.
The tables have no primary key, but ALL have a common field named: IID. These tables are all updated on the network/ server each night. The next morning, each user runs an .exe file that rebuilds the tables with updated data (the data changes everyday...) When I make a primary key in the MSTR table and then create relationships - new tables are created (MSTR1 for example) when the .exe file is activated. It doesn't change the relationship, but new/updated data is now in a different table with a 1 on the end of the name. Which doesn't work because of the related tables are old data...

Is there a work around or do I need to get ahold of our IT folks that manage the servers and all that???

Thanks!!
jw