CODEWITHSUNDEEP
sqlteradata
Dwiyi
Dwiyi

Reputation: 646

Teradata syntax?

is there syntax like 

SET IDENTITY_INSERT <TABLE_NAME> ON | OFF  ------- SQL SERVER

in teradata ??

Upvotes: 0

Views: 719

Answers (4)

Clark Perucho
Clark Perucho

Reputation: 466

We do not have that kind of syntax in Teradata because Teradata handles that differently and it is configured in Column Level. Below is as an example:

CREATE TABLE table01 (
  col_id INTEGER GENERATED BY DEFAULT AS IDENTITY
    (START WITH 1
     INCREMENT BY 1
     MAXVALUE 1000
     NO CYCLE
     ),
  name VARCHAR(10)
)PRIMARY INDEX(col_id);

Key points are below:

  • GENERATED BY DEFAULT - This means that you may or may not insert a value for this column. Meaning, You can insert your own value in this column or if you insert a new row and did not specify a value for this column, a system generated value will be placed to it instead of NULL. You can change it to GENERATED ALWAYS so the system will always decide which values to be inserted to this column.
  • START WITH - Starting Value and also the Lowest Value
  • INCREMENT BY - the Distance between the previous value and the next value
  • MAXVALUE - Highest Value
  • NO CYCLE - Specify this if you do not want the same value to be repeated

Please note that using this feature may restrict you from using some Teradata Utilities and other features as well. For more information, please refer here

Upvotes: 0

Shreyas Khandalkar
Shreyas Khandalkar

Reputation: 3

I don't think there is an option to switch on/off identity column value generation but there are options like GENERATED BY DEFAULT and GENERATED ALWAYS which can conditionally control value generation

Upvotes: 0

Shrikant Soni
Shrikant Soni

Reputation: 2327

Yes, for example

ID_COL INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 100000000 NO CYCLE)

Upvotes: -1

danielsepulvedab
danielsepulvedab

Reputation: 674

As far as I know, in Teradata you can only set the value for an IDENTITYcolumn if it was created specified as GENERATED BY DEFAULT.

If the column was created with the GENERATED ALWAYSoption, no matter the value you set, Teradata will generate a value for that column.

There are other implications of choosing one or the other. More information on this link.

Upvotes: 2

Related Questions