Search code examples
sqlteradatateradata-sql-assistant

Teradata SQL: how to do generated by default identity insert without multiple amps running up the increment triggering error 7545?

I have a table with a smallint identity column generated by default:

MyIdColumn smallint generated by default as identity (start with 1 increment by 1)

The column is expected to be 2 or 3 digits at most, hence the smallint.

I want to do a single row insert; unfortunately when I insert the single row I get error 7545 "numbering for identity column is over its limit". Explain shows that Teradata is doing an all-AMPs retrieve & all-AMPs merge, and a search turns up that on Teradata the many thousands of AMPs are somehow requesting and therefore running up the next identity value (which would I suppose cap out at 32,767).

What are my options here to get this working? Is there a query hint I can specify to tell Teradata just to run the 1-row insert on one AMP and not run up the increment? Do I really need to specify a bigint or other larger data type for what in practice is merely 2 or 3 digit number just to accommodate Teradata?

Solution

  • Try INTEGER datatype which will support MAX value 2,147,483,647 to MIN value -2,147,483,647

    If required create new table and reload the data in new table with below changes

    MyIdColumn INTEGER generated by default as identity (start with 1 increment by 1)