solved Moving data between tables with different field names

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

solved Moving data between tables with different field names

Post by Kadanis »

Hi All

I need to move some legacy data (around 350K rows) from a single table to a series of new tables based on an ID in the legacy table. The thing is, the new tables have a different schema.

Is there any quick way that I can move the data across, or am I stuck with reading in row by row from the old table and inserting row by row into the new tables?

I was hoping something like this:

Code: Select all

 
for ($result->first(); !$result->end(); $result->next()){
$table = $result->table_id;
$id = $result->id;
 
$sql = "INSERT INTO `$table` SELECT 
    DateTime AS date,
    RecipientEmail AS email_address,    
    RecipientID AS recipient_id,
    BounceType AS bounce_type,
    BounceText AS detail,
    ListUpdated AS list_updated
        WHERE ID = '$id';";
 
$db->query($sql);
}
 
could be looped over for each of the new tables and the data moved across, but I'm wrong (or at least its not working for me).

Could anyone suggest anything or am I stuck?

Thanks for reading, and hopefully for replying :)
Last edited by Kadanis on Tue Apr 15, 2008 6:07 am, edited 1 time in total.
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

solvedRe: Moving data between tables with different field na

Post by Kadanis »

Nevermind, figured out a quick solution on the way home.

Just going to duplicate the legacy table and change the schema to match the new format, then break down the data.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Moving data between tables with different field names?

Post by onion2k »

For the record you can do this with a single query..

Code: Select all

INSERT INTO `new_table` (`col_1`, `col_2`, `col_3`) VALUES
SELECT `col_a`, `col_b`, `col_c` FROM `old_table`
..not tested that of course but there's no good reason why it wouldn't work. If you specify the column names they can be in any order you fancy.
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Re: Moving data between tables with different field names?

Post by Kadanis »

Thanks onion, you put me on the right track for the proper solution.

Although to get it working it didn't need the VALUES keyword.

Code: Select all

 
INSERT INTO `new_table` (col1, col2, col3) SELECT col1, col2, col3 FROM old_table WHERE col4 = 'x';
 
Post Reply