I have to migrate data from one database table to another table in
another database where the fields do not match, not even in the same
order, and even if they do match, on occasions the datatypes are
completely different (varchar vs int, varchar vs char, int vs datetime,
etc.).
I am expected to do this immediately so this is an emergency request.
Help!
Thanx
Phil
news:1138030172.364542.280710@g44g2000cwa.googlegroups.com.. .
>I have to migrate data from one database table to another table in
> another database
You can move data from one database to another with syntax such as:
INSERT INTO database2.table2 (field1, field2, field3)
SELECT expr1, expr2, expr3 FROM database1.table1 WHERE blah blah blah
Note the database.table notation. You can specify the databases where each
table lives with this syntax, and thus move values from a table in one
database to a table in a different database.
> where the fields do not match, not even in the same order,
Explicitly naming the fields in the destination table allows you to map the
fields of the select-list to fields of the destination table. And you can
use expressions in the select-list instead of plain field names, so you can
manipulate the values appropriately to fit into the datatypes of the
destination.
> and even if they do match, on occasions the datatypes are
> completely different (varchar vs int, varchar vs char, int vs datetime,
> etc.).
Well, there are ways to convert from one datatype to another in most cases.
Though sometimes there might be some information lost.
For instance, if a varchar contains the string '123ABC' and its destination
is an integer field, you can convert the string to its initial integer part
with expressions such as: SELECT CAST('123ABC' AS UNSIGNED) or SELECT
FLOOR('123ABC'). This yields the integer '123', but loses the 'ABC' part.
Or you could use the HEX() function to create a reversible integer
representation of the whole string. It's not clear from your description
what is the nature of the data in these fields, so it's not possible to give
a recommendation that will work in all cases.
You also mention converting an integer to a datetime. How is this done? An
integer can be used to represent datetime information in many ways. Number
of seconds since 1/1/1970 12:00:00 is one standard way. Is that what the
integer in your database represents? (hint: the FROM_UNIXTIME() function
outputs a human-readable format for the data, based on an integer in this
format)
Read the following pages for more documentation on MySQL functions that may
help:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/mathematical-function s.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
Basically, your task is to make policy decisions about how the data in these
fields map into the destination database. Sometimes there may be
unavoidable loss of information, unless you can also modify the schema of
the destination database.
Once you have the data mapping decisions made, it's more straightforward to
make expressions to convert the values to something that is compatible with
the datatypes of the destination fields.
> I am expected to do this immediately so this is an emergency request.
It is unreasonable to expect this task to take *zero* time.
Regards,
Bill K.
"It is unreasonable to expect this task to take "zero" time."
Unreasonable yes. Did that ever stop the government before?
Phil