"Neculai Macarie" <[EMAIL PROTECTED]> wrote on 05/12/2005 03:26:33 AM:
> > >> Not that I'm aware of. What type of conversions are you doing that
you
> > >> need 30,000 use vars? An easy solution would be to try it and find
out
> :)
> > >
> > > I need to move multiple linked entries (in around 12 tables) from
one
> > > running server to another. I'm using auto_increment's all over the
place
> > > and
> > > I have the following problem: I need to move (no replication
possible)
> the
> > > information in those 12 tables to an identical functioning system
(same
> > > software, same db, but other server) and I need to preserve the
> relations
> > > between the tables. The problem that I have is that the systems
evolve
> > > independently and I can have the same id for 2 different entries on
the
> > > system (e.g. on the first system I can have the id 10 for User1, but
on
> > > second system id 10 would be for another user).
> > >
> >
> > Perhaps after you dump the structure and data into sql files,
> > you could remove temporarily the extra attribut auto increment to
those
> > columns.
> > Then start inserting.
> > After that add the auto increment attribut again.
> > OR
> > use bulkcopy(?) if im not mistaken, or any other methods to copy as it
is
> to
> > new dbservers
> > (compress it first).
>
> Donny,
>
> Thanks for the answer. Dumping the structure and data alone (mysqldump
> style) is not helping me because the tables are linked and I must update
the
> linked tables with the new id's.
>
> --
> <mack />
>
Here is a summary of how I have merged hierarchical data structures in the
past. I start by adding a column or two to my destination data tables for
each table in the tree I need to reconstruct. The first new column (I
usually call it something like "old_ID") holds the original PK of the
source record. The second (if necessary) will hold the original PARENT's
PK value.
As an example, let's imagine that I need to merge a table that looks like
{ID, PARENT_ID, <DATA columns>} into a new table with the same structure.
In this table PARENT_ID points to some other record in the same table. I
would add my two columns like this
ALTER TABLE new_table add old_ID int, add old_parentid int;
Then, I would need to map the INSERT like this
INSERT new_table (old_ID, old_parentid, <data columns>)
SELECT old_table id, parent_id, <data columns>;
Then I go back and update the PARENT_ID of the records in new_table with
the new ID value of their OLD parent records.
UPDATE new_table nt1
INNER JOIN new_table nt2
ON nt2.old_ID = nt1.old_parentid
SET nt1.PARENT_ID = nt2.ID
WHERE nt1.old_parent_ID is not null;
This re-creates the parent-child relationship that used to exist in
old_table by filling in the new values for the PARENT_ID that were
auto-generated when the old records were merged. Repeat this for each
table in your hierarchy. In this example both parent and child records
were from the same table but they didn't need to be. nt1 is the alias for
the "child" table while nt2 represents the "parent" table.
You will need to disable any FK constraints (InnoDB) while you rebuild
your parent-child relationships but once you have filled in the child's
parent_id field, you should be able to re-enable those keys and move on to
the next level. If you get an error, check your data. Working from the
top down, you should not create too many issues, especially if your data
was well-organized to start with. Once you have regenerated your
parent-child links to use the new auto_increment values, you can start
dropping the old_* columns to recover that space and optimize your tables
at the same time (thanks to the behavior of the ALTER TABLE).
ALSO, before you start, make a backup of your data (mysqldump works well
for most people). That way if you hose it up in some major way, you can at
least get back to where you started without too much pain.
If you have any questions, I will be lurking.... ;-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
