Quoth Doug Laidlaw <laidlaws (AT) myaccess (DOT) com.au>:
Let me suggest that you NOT try to do the conversion as one big step;
down that path lies madness, irrespective of where you're coming and
going.

I have fairly regularly been involved in converting data into
PostgreSQL databases, and have found it most useful to go through a
phased approach.

Phase I: Load data in a completely unvalidated form

For this purpose, practically any input method will do. Most fields
should be of type TEXT to allow any kind of crud to make it over.
This part should be as automated as you can get, I think the "most
modern" thing is the converter at GBorg...

<http://gborg.postgresql.org/project/mysql2psql/projdisplay.php>

At this point, what you have is the original data, which is probably
pretty borked as far as your application is concerned. MySQL is
fairly much _terrible_ from a data validation perspective, allowing
invalid dates and the like.

Phase II: Cleanse the data

This may actually involve multiple steps.

The notion is that you take the imported data, and move it over to a
new set of tables, fixing what's broken.

You define a new table that has strongly typed fields (e.g. - date
stamps and such) and fix the data errors.

In my most recent version of this, I had to do a lot of work to
reformat telephone numbers. The "legacy" application was very
easy-going about what crud you could toss in; the destination
application requires that the formats conform with ITU standards. I
had no issues with timestamps being drastically wrong, happily.

It may take many queries to fix things up.

Ideally, this generates a _new_ set of tables, so that you leave the
data that you imported from the old system pristine so you can look
back and compare in any auditing processes that may need to be done...

Phase III: Load into the destination application's tables.

Once all the data is cleansed, it should be easy to load it into the
tables for the new application, in one "fell swoop."

insert into app_table1 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table1;
insert into app_table2 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table2;
insert into app_table3 (f1,f2,f3,f4) select f1,f2,f3,f4 from phase2.app_table3;

All being said, that's not _quite_ what I do. I'm expecting to do a
_lot_ of data conversions specific to a certain application. As a
result, I have created stored procedures specific to that application.
I have 4 kinds of objects, which interlink pretty tightly. Those
linkages have to get created in the new system, and actually, there
are further additional linkages (that may be treated as "magic" :-))
that may not even have existed in the old system.

I'll have tables:

object_a;
object_b;
object_c;
object_d;

And linkages...

a_b_links;
a_c_links;
d_c_links;

And create the objects via:

select create_a(fields) from phase2.object_a;
select create_b(fields) from phase2.object_b;
select create_c(fields) from phase2.object_c;
select create_d(fields) from phase2.object_d;
select link_a_b(a,b) from phase2.a_b_links;
select link_a_c(a,c) from phase2.a_c_links;
select link_d_c(d,c) from phase2.d_c_links;

That meant writing 7 stored procedures, one for each object/linkage
type...

The result of that is a _really_ clean set of data.

In any case, if you try to do it all via one "magick step," you'll
have a lot of trouble getting it all right.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/spiritual.html
The difference between a child and a hacker is the amount he flames
about his toys. -- Ed Schwalenberg