Teradata IDENTITY columns

Database
Teradata Employee

Teradata IDENTITY columns

I have searched for an answer to my query and surprisingly have found no answer. I do find it a bit difficult to believe that almost 100% of people INSERT VALUES as opposed to INSERT SELECTing.

So, my query is:

CREATE SET TABLE cdm.acl_test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ID_COL INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE -2147483647
MAXVALUE 100000000
NO CYCLE),
CIDPERSID INTEGER,
PRDID INTEGER)
UNIQUE PRIMARY INDEX ( ID_COL );

Now... How do I INSERT using a select statement?

What I have done is:

INSERT INTO tablename
SELECT 1
,CUSTOMER_ID
,PRODUCT_ID
FROM CUSTOMER_PRODUCTS;

This seems to have worked, but I just want to ensure this is correct.

Many thanks!!!
6 REPLIES
Enthusiast

Re: Teradata IDENTITY columns

Yes that would work. You could also use the restriction, where you don't select the Identity column in your insert list:

INSERT INTO test12 (eno, ename) SELECT eno, ename FROM test11
Teradata Employee

Re: Teradata IDENTITY columns

Thanks for that. This next one is going to seem a silly question.

If I have a table of 10 columns and use the following INSERT:

INSERT INTO tablename (col1,col3)
Select col1
,col3
FROM tablename2;

Will this work? I was under the impression that when you insert into a table, you need to specify a value or select column for every column in the table.

If this is not the case, then I think my understanding of UPSERT operations are more complete.

Thanks!

PS I had tried the method of simply not selecting the identity column but this did not work. I had not, however, specified the columns to populate, though, either, so will try this tomorrow at work.
Highlighted
Junior Contributor

Re: Teradata IDENTITY columns

Hi Andrew,
of course this will work.
.For any unused column it's DEFAULT or NULL will be used, a NOT NULL column will result in an error message,

Dieter

Re: Teradata IDENTITY columns

On the same topic... I have a seemingly strange case where the Identity values skipped a large number of possible values.

(1) Why did this happen and (2) what can be done to make it number sequentially, please?

The Identity column is defined as:
ID_COL INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 100000000 NO CYCLE)

One of the jump sections of the ID column is: (and it jumped multiple times to a very high number)
79
80
81
82
83
84
100001
200001
200002
200003
200004
200005
Enthusiast

Re: Teradata IDENTITY columns

When records are stored with an identity column, the system gets a batch of numbers for each AMP (assuming they go in by Insert Into/ Select). There are a number of reasons why they may not be consecutive, and there is no guarantee that the first input record is number 1, second number 2, etc. In fact it is extremely unlikely. This is described in SQL Ref/ Data Definition Statement/ Create Table/ Column Clause.

If you want them numbered, you can either put them in a work table and number them with Row_Number, use an Inmod to number them on input, or insert each individually using Bteq. For high volumes and option 3, kick off the process, go get married/ have children and check its process just before your retirement party! (So not recommended.)

Re: Teradata IDENTITY columns

Hi All, I have my identity column in negative (as stated above). When I try to insert a record, it goes to number lesser than minimum limit..

ID_NUM BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH -99999999999999999
INCREMENT BY 1
MINVALUE -99999999999999999
MAXVALUE -1
NO CYCLE)
Whenever I insert a record, id_NUM value show as -10000000000000000 (-99999999999999999 - 1 instead of -99999999999999999+1).. (note number of 9s is 17 here)

Can you explain why? But this is not the case with identity defined as -9999999999999999 as lower limit.. (note - number of 9s is 16 here)