MySQL table merge

June 14, 2011 2:01 PM

How does one go about merging disparate MySQL tables? (Blockheaded amateur question)

I've tried reading about this online, but no luck in figuring it out so far. I have four different tables, each with their own primary key. I need to map the differently-named fields from each table onto a fifth table, which will give all the entries new primary-key ID numbers while conserving their old ID numbers. Each of the four tables has different columns and some columns only exist in one table; these will be left NULL in the "result" table.

My question is this: is there some command that I can feed a map of the different columns and have it do it magically, or do I have to manually write some kind of copy-paste algorithm? If it's easier to do this with a PHP script of some type, I can do that instead-- the tables aren't live and performance of this task isn't an issue. I've also read about the UNION command, but I don't know how to dump that into a table of its own, or if it can do everything I need it to do.

I cannot understand the written tutorials in a way that would permit me to answer this question by reading them. I don't know why this is (allergies? insufficient brain power?), but I need someone to explicitly explain the answer to me. Thank you for your time.

posted by Electrius to computers & internet (12 answers total)

insert into table_5 (relevant_fields) (select relevant_fields_in_same_order from table_n)
posted by missmagenta at 2:10 PM on June 14, 2011

The first list of relevant fields is the field names from table_5, and the second list is the field names from table_n?
posted by Electrius at 2:25 PM on June 14, 2011

First list is the destination fields in target table and the second list (the select statement) is from the source table.
posted by babbyʼ); Drop table users; -- at 2:31 PM on June 14, 2011 [1 favorite]

Although this can be done with a PHP script, doing so using SQL statements will be cleaner, more efficient, and most importantly way faster than using PHP.

Feel free to post an example statement so we can troubleshoot for you. For future reference, Stack Overflow (using the MySQL tag) is a great way to get fast answers.
posted by babbyʼ); Drop table users; -- at 2:34 PM on June 14, 2011

You could do this with a DTS/SSIS package very easily if you have Enterprise Manager/Management Studio
posted by gatsby died at 2:41 PM on June 14, 2011

Perhaps I'm not understanding you but why would you want to do this at all? Seems to me the data is perfectly sound as it is and you simply need queries to extract the desired combinations.

The fact that some columns only exist in some tables would normally be a strong hint to leave well enough alone. Then again you understand your situation and I don't.
posted by epo at 3:26 PM on June 14, 2011

Perhaps I'm not understanding you but why would you want to do this at all? Seems to me the data is perfectly sound as it is and you simply need queries to extract the desired combinations.

"Not combining" is one of the options I considered but there needs to be a unified search for the project specification. That seems to be easier to write if the tables are merged. I guess it might be easier to leave them separate.

So something like insert into new_table (field1, field2) (select field3, field4 from old_table) will cause field3 and field4 to be dumped into field1 and field2?
posted by Electrius at 3:30 PM on June 14, 2011

Again short on supporting detail but a unified search could easily be a unified query (or more likely some kind of view).

Your insert statement is probably the kind of thing you want in order to get a unified table. You seem to be proposing what is called denormalising a database design, sometimes this is useful for improving performance, sometimes it turns a good design into a bad one.

Take a good backup before you begin and create separate tables with your new design, that way if things go wrong you can backtrack painlessly. But first, see if views could do what you need.
posted by epo at 3:49 PM on June 14, 2011

On preview, what epo said. If you're not familiar with normalization, here's some reasons for it.
posted by juv3nal at 3:59 PM on June 14, 2011

but there needs to be a unified search for the project specification

That's why SQL supports JOINs. Do not under any circumstances denormalize your data. Every table has a single purpose.
posted by Civil_Disobedient at 6:39 PM on June 14, 2011

"Not combining" is one of the options I considered but there needs to be a unified search for the project specification.

Write a view that creates the "table" you want to search, and search it.

Advantages: everything else continues to work as before, and you have unified searching.
posted by orthogonality at 8:24 PM on June 14, 2011

Using MySQL > 5? This is exactly what views are for.
posted by col at 3:32 AM on June 15, 2011