All Forums Database
24 Dec 2007
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 tablenameSELECT 1,CUSTOMER_ID,PRODUCT_IDFROM CUSTOMER_PRODUCTS;This seems to have worked, but I just want to ensure this is correct.Many thanks!!!

Andrew Livingston EMEA - Customer Education Consultant Teradata UK 206 Marylebone Road London NW1 6LY United Kingdom M: +44 7785 971 080 E-mail: Andrew.Livingston@Teradata.com
nithyanandam 65 posts Joined 10/04
26 Dec 2007

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

26 Dec 2007

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,col3FROM 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.

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

dnoeth 4628 posts Joined 11/04
27 Dec 2007

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

Dieter

Andrew_S 4 posts Joined 02/07
14 Sep 2009

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)798081828384100001200001200002200003200004200005

Jimm 298 posts Joined 09/07
16 Sep 2009

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.)

MewithTeradata 3 posts Joined 06/10
16 Dec 2010

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)

You must sign in to leave a comment.