[RESOLVED] Moving records from one table to another

spiritssight

Hello,

The question I have is:

I have a table full of information all together now this information needs to be seperated to the correct tables like address, phones, contacts, biz hours, services, finanices. some of these tables will be used all the time and others not much. how would some one go about this?

Thanks very much for the help!

Sincerely,
Christopher

void_function

Look into INSERT ... SELECT statement. It allows you to insert into one table the fields fetched from another:

http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

spiritssight

If you would not mind could you show me a example or point me to a sample as I learn from looking at the code then trying to understand the manual?

Thanks alot

spiritssight

Ok let me see if I can say this better so I can more help, the Table 1 has 32 fields now by time I add all the other information that is not in there we are talking over 100 fields so I am going to break it up while its small into sections, now when it moves it from the table 1 I want it to delete from there after it has been successfully added to the to the other tables. Can some one please point me in the direction to a example or provide one for me please?

DATABASE Table 1
EIN PNO Street City State Postal_Code Phone Fax etc etc etc etc etc

DATABASE table_2
EIN PNO Street City State Postal_Code
DATABASE table 3
phone fax etc etc
DATABASE Table 4
etc etc etc etc

Also is there a scripte that would help make write the queries easer like read the table names and do the code so I don't have to do that every time I want to query the tables?

Thanks alot
Christopher

tomhath

http://www.1keydata.com/sql/sqlinsert.html

Most DBMS administration tools will let you view individual tables. Beyond that you need to write SQL.

void_function

For example:

INSERT INTO table_2 (id, EIN, PNO, Street, City, State, Postal_Code)
SELECT id, EIN, PNO, Street, City, State, Postal_Code
FROM table_1

INSERT INTO table_3 (id, phone, fax, etc...)
SELECT id, phone, fax, etc...
FROM table_1

Just keep in mind that you need to link the fields between tables, like by copying the ID from table_1 into each of the tables, as given in the examples above. You can use that as primary key in tables_2 _3 etc...

spiritssight

void_function:

is this done with more then one query or one query?

Ok, I have 5 tables
first one is irs_query
say tble 1 had fields id,1,2,4,5,6,7,8,9,10
now the remaining are the tables that tble1 going to be split into
tble 2 is connected to tble 3
id,1,
tble 3 is connected to tble 4,5
id,2,3,
tble 4
id,4,5,6,
tble 5
id,7,8,9,10

what is the best way to do this task, I am going to have 6 more tables that I have not listed or created yet that will connect to tble 2

after it has been moved to the tbles then the one in tble 1 needs to be deleted.

Thanks for your help folks