Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- The ultimate migration nightmare - need help
January 23, 2006, 3:29 pm
rate this thread
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,
I am expected to do this immediately so this is an emergency request.
Re: The ultimate migration nightmare - need help
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.
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
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
Read the following pages for more documentation on MySQL functions that may
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.
It is unreasonable to expect this task to take *zero* time.
- » help with SQL coding question - 3 tables with outer join needed
- — Next thread in » MySQL Database Forum
- » unix_timestamp() & retrieving historical rate data
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum