Informix® provides two
SQL instructions to export / import data from / into a database table:
The UNLOAD instruction copies rows from a database table into a text
file and the LOAD instruction inserts rows from a text file into a
database table.
Microsoft™ SQL SERVER
has LOAD and UNLOAD instructions, but those commands are related to
database backup and recovery. Do not confuse with Informix commands.
Solution
LOAD and UNLOAD instructions are
supported; note the following;
- The LOAD instruction does not work with tables using emulated
SERIAL columns because the generated INSERT statement holds the "SERIAL"
column which is actually a IDENTITY column in SQL SERVER. See the
limitations of INSERT statements when using SERIALs.
- With Microsoft SQL
SERVER versions prior to 2008, Informix DATE
data is stored in DATETIME columns, but DATETIME columns are similar
to Informix DATETIME YEAR
TO FRACTION(3) columns. Therefore, when using LOAD and UNLOAD, those
columns are converted to text data with the format "YYYY-MM-DD hh:mm:ss.fff".
However, since SQL SERVER 2008, Informix DATE
data is stored in SQL SERVER DATE columns, so the result of a LOAD
or UNLOAD statement is equivalent when using a DATE column with SQL
SERVER 2008.
- With Microsoft SQL
SERVER versions prior to 2008, Informix DATETIME
data is stored in DATETIME columns, but DATETIME columns are similar
to Informix DATETIME YEAR
TO FRACTION(3) columns. Therefore, when using LOAD and UNLOAD, those
columns are converted to text data with the format "YYYY-MM-DD hh:mm:ss.fff".
With SQL SERVER 2008, Informix DATETIME
data is stored in SQL SERVER DATETIME2(n<=5) or TIME(n<=5) columns.
Concerning DATETIME2(n<=5) columns, the result of LOAD and UNLOAD
is equivalent to Informix DATETIME
columns, as long as the original Informix type
starts with the YEAR qualifier. The text data will be "YYYY-MM-DD
hh:mm:ss.<fraction-digits>", where fraction-digits depends
on the precision (n) of the DATETIME2(n) column. Concerning TIME(n)
columns, the type is converted to an Informix DATETIME HOUR TO SECOND or FRACTION(n).
The text data will be "hh:mm:ss.<fraction-digits>", where fraction-digits depends
on the precision (n) of the TIME(n) column.
- When using an Informix database,
simple dates are unloaded with the DBDATE format (ex:"23/12/1998").
Therefore, unloading from an Informix database
for loading into a Microsoft SQL
SERVER database is not supported.