AWS Schema Conversion Tool SCT fails to Convert Table DDL from SQL Server to Amazon Redshift
AWS Schema Conversion Tool SCT fails to convert table DDL SQL script or CREATE TABLE command from SQL Server database to Amazon Redshift database when the table has an identity column with data type smallint.
Here is the SQL DDL create command when I use Amazon SCT Schema Conversion Tool for migration of a SQL Server database schema objects including database tables to Amazon Redshift database.
CREATE TABLE IF NOT EXISTS ofsa.appointmentitemnumber
(
id_appointmentitemnumber INT IDENTITY(-32768, 1) NOT NULL,
appointmentitemnumber BIGINT ENCODE ZSTD
)
DISTSTYLE KEY
DISTKEY
(
id_appointmentitemnumber
);
As seen below AWS Schema Conversion Tool converted SQL Create Table command for a specific table from SQL Server database to be created on Amazon Redshift database as follows
When the database developer tries to apply the above SQL DDL Create Table command provided by AWS Schema Conversion tool (SCT), the result will be unsuccessfull. Unfortunately the output message is not really helpful to understand the real reason behind the SQL error preventing table creation on target Amazon Redshift database.
What the Schema Conversion Tool misses here in this table create SQL script is that Amazon Redshift SQL database engine does not allow to create a table with identity column of data type SmallInt.
The identity column data type should be of type Int or BigInt but not SmallInt on the other hand.
If the Amazon Redshift database programmer executes the CREATE TABLE command manually on the target Redshift database, developer will experience following error which provides some what more information about the nature of the SQL error.
[Amazon](500310) Invalid operation: invalid type specified for identity column "id_appointmentitemnumber"; [SQL State=42601, DB Errorcode=500310]