Handling Column Types when Replacing Tables
When replacing a table, the column definition of the source file (being uploaded) must match that of the destination data source (the existing table being replaced). However, the following type conversions are allowed:
- When a numeric column in the uploading table has a smaller type, such as INTEGER, and the existing table (being replaced) has a larger numeric type, such as LONG, the existing table retains its column type, and the smaller values are inserted.
- When the existing table (being replaced) has a STRING column, it retains its column type as STRING, but any values can be inserted. For example, a numeric column or date column in the uploaded table will become STRING in the existing table.
The table below describes the allowed behavior.
| Source column type | Can be inserted into destination column of the following types |
| SHORT_INTEGER | SHORT_INTEGER, INTEGER, LONG_INTEGER, FLOAT, DOUBLE or STRING |
| INTEGER | INTEGER, LONG_INTEGER, FLOAT, DOUBLE or STRING |
| LONG_INTEGER | LONG_INTEGER, FLOAT, DOUBLE or STRING |
| FLOAT | FLOAT, DOUBLE or STRING |
| DOUBLE | DOUBLE or STRING |
| DATE | DATE or STRING |
| TIME | TIME or STRING |
| DATE_TIME | DATE_TIME or STRING |
| BOOLEAN | BOOLEAN or STRING |
| STRING | STRING |
When the uploading TAB file replaces an existing TAB file, the above does not apply. There is strict column type matching, so the column types of the two TAB files must match exactly. Only the length of the string column can vary.
When the uploading table replaces an existing table, the length of a string column may increase to fit the length of the strings in the uploading table. For TAB files, Spectrum Spatial can increase the length up to a maximum of 254 characters. For SQL server, the string column can increase up to 1024 characters, and if the text exceeds that, then to the maximum that SQL server supports.