Page 1 of 1

solved Moving data between tables with different field names

Posted: Mon Apr 14, 2008 11:54 am
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 :)

solvedRe: Moving data between tables with different field na

Posted: Mon Apr 14, 2008 2:02 pm
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.

Re: Moving data between tables with different field names?

Posted: Mon Apr 14, 2008 3:01 pm
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.

Re: Moving data between tables with different field names?

Posted: Tue Apr 15, 2008 6:06 am
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';