Data types mapping for database migration.
This article discusses how you can match data types when converting from one database to another.
In some cases, data from old database fields must be moved into fields in the new database. To transfer data from the source database fields to the destination ones requires a data mapping functionality. This may be an abstract understanding of how data will be exported and then imported into the target fields in the destination database.
Data types mapping is the process of data type's compatibility between original and target fields. With such feature it is easily to match one data type to its appropriate and relative equivalent in the resultant fields.
To simplify the conversion process we implemented this feature in all DBConvert tools. Our programs automatically transform types from one format to another. All you need is to choose required data types for the destination fields or the closest data types for the source type. In other words, you get the ability to change data type for all your destination fields at the customization stage in one touch.
The following table shows the data type mapping for MySQL.
Text |
|
| CHAR | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| VARCHAR | VARCHAR, CHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| TINYBLOB (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| TINYTEXT (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| BLOB (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| TEXT | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| MEDIUMBLOB (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| MEDIUMTEXT (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| LONGBLOB (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
| LONGTEXT (text) | CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT |
Numeric |
|
| TINYINT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| SMALLINT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| MEDIUMINT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| INT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| BIGINT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| FLOAT | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| DOUBLE | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| DECIMAL | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL |
Date time |
|
| YEAR | YEAR, TIMESTAMP, DATETIME |
| DATE | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
| TIME | TIME, TIMESTAMP, DATETIME |
| TIMESTAMP | YEAR, DATE, TIME, TIMASTAMP, DATETIME |
| DATETIME | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
Binary |
|
| TINYBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| BLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| MEDIUMBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| LONGBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| VARBINARY (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
Possible data types mapping for MS Access is shown below.
Text |
|
| CHAR | CHAR, MEMO, TEXT |
| TEXT | CHAR, MEMO, TEXT |
| MEMO | CHAR, MEMO, TEXT |
Numeric |
|
| BYTE | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| SHORT | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| INTEGER | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| LONG | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| COUNTER | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| SINGLE | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| DOUBLE | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
| CURRENCY | BYTE, SHORT, INTEGER, LONG, COUNTER, SINGLE, DOUBLE, CURRENCY |
Date time |
|
| YEAR | YEAR, TIMESTAMP, DATETIME |
| DATE | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
| TIME | TIME, TIMESTAMP, DATETIME |
| TIMESTAMP | YEAR, DATE, TIME, TIMASTAMP, DATETIME |
| DATETIME | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
Binary |
|
| TINYBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| BLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| MEDIUMBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| LONGBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| VARBINARY (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
MSSQL field mapping is shown in the given below table.
Text |
|
| NCHAR | NCHAR, NVARCHAR |
| NVARCHAR | NCHAR, NVARCHAR |
Numeric |
|
| TINYINT | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| SMALLINT | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| INT | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| SMALLMONEY | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| BIGINT | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| FLOAT | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| REAL | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| NUMERIC | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| DECIMAL | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
| MONEY | TINYINT, SMALLINT, INT, SMALLMONEY, BIGINT, FLOAT, REAL, MONEY, DECIMAL, NUMERIC |
Date time |
|
| YEAR | YEAR, TIMESTAMP, DATETIME |
| DATE | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
| TIME | TIME, TIMESTAMP, DATETIME |
| TIMESTAMP | YEAR, DATE, TIME, TIMASTAMP, DATETIME |
| DATETIME | YEAR, DATE, TIME, TIMESTAMP, DATETIME |
Binary |
|
| TINYBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| BLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| MEDIUMBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| LONGBLOB (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| VARBINARY (binary) | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
All possible variants for FoxPro data mapping is given in the following table.
Text |
|
| C | V |
| V | C |
Numeric |
|
| B | F, N, I, Y |
| F | B, N, I, Y |
| N | F, B, I, Y |
| I | F, N, B, Y |
| Y | F, N, I, B |
Date time |
|
| D | T |
| T | D |
Field mapping for PostgreSQL:
Text |
|
| CHAR | VARCHAR, BPCHAR, TEXT |
| VARCHAR | CHAR, BPCHAR, TEXT |
| BPCHAR | VARCHAR, CHAR, TEXT |
| TEXT | VARCHAR, BPCHAR, CHAR |
Numeric |
|
| BIGINT | INTEGER, SMALLINT, NUMERIC, REAL, DOUBLE PRECISION, SERIAL, BIGSERIAL |
| INTEGER | BIGINT, SMALLINT, NUMERIC, REAL, DOUBLE PRECISION, SERIAL, BIGSERIAL |
| SMALLINT | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION, SERIAL, BIGSERIAL |
| NUMERIC | INTEGER, SMALLINT, BIGINT, REAL, DOUBLE PRECISION, SERIAL, BIGSERIAL |
| REAL | INTEGER, SMALLINT, NUMERIC, BIGINT, DOUBLE PRECISION, SERIAL, BIGSERIAL |
| DOUBLE PRECISION | INTEGER, SMALLINT, NUMERIC, REAL, BIGINT, SERIAL, BIGSERIAL |
| SERIAL | INTEGER, SMALLINT, NUMERIC, REAL, DOUBLE PRECISION, BIGINT, BIGSERIAL |
| BIGSERIAL | INTEGER, SMALLINT, NUMERIC, REAL, DOUBLE PRECISION, SERIAL, BIGINT |
Date time |
|
| ABSTIME | DATE, TIMESTAMP |
| DATE | ABSTIME, TIMESTAMP |
| TIMESTAMP | ABSTIME, TIMESTAMP |
In case you need to apply a mapping for all fields in a database, use Global Mapping feature. This feature can be set when you click on the database itself at the customization stage. You simply choose from what type to what you want to convert and our program do the rest.
DBConvert Product Line
