Something I’m working on at the moment requires a rather large collection of data to be moved from several places into one clever little table that I’ve created. It’s going to consolidate functionality and make things much easier to script in a few different environments.

Gathering all of this data together has been made pretty damn easy by using a few choice SQL queries. A few months ago I would probably have written a Perl script to do this instead, but that’s really not necessary.

This first query performs a SELECT on one table and uses the values to populate the INSERT statement values. It’s quite simple because all of the required data is in one table on the same database as the table it’s being copied to. All field, table, and database names have been changed to protect the innocent.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, email_address_1, email_type FROM prefs WHERE email_address_1 IS NOT NULL;

The second query is a bit more complicated. It performs a SELECT on a table in one database that does an INNER JOIN on a table in another database and then an INSERT to get all of the collated data into a different table in the first database.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, people.email, email_type FROM prefs INNER JOIN other_db.people ON prefs.person_id=people.id WHERE prefs.password_reminder=1;

I was quite pleased to find this worked first time when I tested. It doesn’t seem complicated to me now, but I know it would have done before I started working where I do now.