All Forums Database
TD_Arch 35 posts Joined 07/05
12 Aug 2005
Identity Column Not Coming in a sequence. Need Suggestion.

I have to generata a Sequence of numbers as the primary index.But , after the insertions of some sample data, i find that it is not completely in sequence. I get numbers from 1 thru 10, then numbers from 1000001 thru 1000010, and so on ...I understand that this is because the identity column values are generated on an amp-local basis, and hence the result. But, somehow we need the data to be essentially in sequence for more than 500 million rows.Is there any way to do this?Thanks.

MNagat 7 posts Joined 03/06
17 Mar 2006

I'm a little unsure of using IDENTITY even with DECIMAL(18) because of this parallel implementation, since the 5753 error - Numbering for Identity Column columnname is over its limit - happens if the column is INTEGER when there's still room for lots of values.In other words, if the upper bound has been reached but there are still gaps between existing values, does TD use those values (as we desire) or can that same error happen?Other question is if the behavior varies depending if you INSERT with VALUES or INSERT SELECT.

williamdieter 15 posts Joined 03/06
21 Mar 2006

Try this:insert into rahulj.partitbl (unique_column,mycolumn)select row_number() over (order by mycolumn),mycolumn from rahulj.partitbl;

TD_Arch 35 posts Joined 07/05
22 Mar 2006

This also works:insert partitbl (k)SELECT sum(1) over (rows unbounded preceding) As ROW_ID from dbc.databases;

davli 2 posts Joined 03/06
23 Mar 2006

hi,In Teradata, is there an equivalent of the Sequence feature such as in Oracle? Identity is good but currently you cannot copy table with identity column, also it's less than ideal that you have gaps in the numbers assigned. thank you

TD_Arch 35 posts Joined 07/05
24 Mar 2006

No, you dont have anything thats same as Sequence of Oracle.

davli 2 posts Joined 03/06
24 Mar 2006

thanks,now in Teradata V2R6 we cannot directly copy a table containing Identity column - is there any recommended workaround that would allow us to copy such a table in 2 or 3 steps? but still preserving its Identity column, AND still preserving the actual ID's assigned.thank you

vikas sharma 2 posts Joined 03/06
29 Mar 2006

Hi We too have spent weeks trying to find a sequence eqivalent in Teradata .Apparently there is none. If the requirement is not sequqnce but unique number which should not be repeated in any case then u can implement this way :CREATE SET TABLE cfdw2_trr_tbls.davidtest_all_obligors ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( oblgr_num INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 2147483647 NO CYCLE), OBLGR_NUM_OLD INTEGER)UNIQUE PRIMARY INDEX ( oblgr_num );UPI will ensure that it is never repeated. If u need sequence solution then u need to create a seperate table : sequence and write trigger (after insert on the table for which u wish to implement it and within this proc u need to implement the logic ). I have done it and if u want i can send u the code as well .RgdsVikas

young 14 posts Joined 03/06
03 Apr 2006

FYI...SELECT RANK(employee_number) ,employee_number ,last_nameFROM CUSTOMER_SERVICE.employee;Rank(Employee_Number) 1 Employee_Number 1025 Last_Name ShortRank(Employee_Number) 2 Employee_Number 1024 Last_Name BrownRank(Employee_Number) 3 Employee_Number 1023 Last_Name RabbitRank(Employee_Number) 4 Employee_Number 1022 Last_Name MachadoRank(Employee_Number) 5 Employee_Number 1021 Last_Name MorrisseyRank(Employee_Number) 6 Employee_Number 1020 Last_Name CharlesRank(Employee_Number) 7 Employee_Number 1019 Last_Name KubicRank(Employee_Number) 8 Employee_Number 1018 Last_Name RatzlaffRank(Employee_Number) 9 Employee_Number 1017 Last_Name RunyonRank(Employee_Number) 10 Employee_Number 1016 Last_Name RogersRank(Employee_Number) 11 Employee_Number 1015 Last_Name WilsonRank(Employee_Number) 12 Employee_Number 1014 Last_Name CraneRank(Employee_Number) 13 Employee_Number 1013 Last_Name PhillipsRank(Employee_Number) 14 Employee_Number 1012 Last_Name HopkinsRank(Employee_Number) 15 Employee_Number 1011 Last_Name DalyRank(Employee_Number) 16 Employee_Number 1010 Last_Name RogersRank(Employee_Number) 17 Employee_Number 1009 Last_Name LombardoRank(Employee_Number) 18 Employee_Number 1008 Last_Name KanieskiRank(Employee_Number) 19 Employee_Number 1007 Last_Name VillegasRank(Employee_Number) 20 Employee_Number 1006 Last_Name SteinRank(Employee_Number) 21 Employee_Number 1005 Last_Name RyanRank(Employee_Number) 22 Employee_Number 1004 Last_Name JohnsonRank(Employee_Number) 23 Employee_Number 1003 Last_Name TraderRank(Employee_Number) 24 Employee_Number 1002 Last_Name BrownRank(Employee_Number) 25 Employee_Number 1001 Last_Name Hoover

young 14 posts Joined 03/06
10 Apr 2006

The Teradata RDBMS does not have a direct SQL function to implement System-Assigned Keys. With Teradata V2R5, the SQL Identity feature can be used to “generate a table-level unique primary index” which is similar to a system-assigned key.Another technique is to use an INMOD routine that assigns a unique value (system-assigned Primary Key) when loading the data with one of the Application Utilities (e.g., FastLoad, MultiLoad, or TPump.)An INMOD routine is a user-written program that pre-processes the data before passing it to the application utility.

Subbu 1 post Joined 01/07
28 Jul 2008

Hi Vikas,Yours->If u need sequence solution then u need to create a seperate table : sequence and write trigger (after insert on the table for which u wish to implement it and within this proc u need to implement the logic ). I have done it and if u want i can send u the code as well .->Could you please help me to send your code to generate unique id to ....vs1000@rediffmail.comsubbu

Adeel Chaudhry 773 posts Joined 04/08
28 Jul 2008

Hello,Yes, there is no direct/1-to-1 equivalent in Teradata for Oracle sequencers.What you can do, is to write a simple trigger as follows:Suppose we have table definition as follows:CREATE TABLE Table1 (Sequence1 Integer, Col1 Integer, Col2 Integer);And a temporary table as follows:CREATE VOLATILE TABLE tempTable1 (CurrentMaxValue Integer) ON COMMIT PRESERVE ROWS;And we have an INSERT TRIGGER on this table as follows:- INSERT tempTable1 (CurrentMaxValue) SELECT Max(Sequence1) + 1 FROM Table1;- UPDATE Table1 SET Sequence1 = tempTable1.CurrentMaxValue WHERE (add parameters from newly added row);- Empty the temporary table tempTable1.I haven't tried it, but it should work if it doesn't hit any INSERT trigger's limitation. HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

miazo-6824 5 posts Joined 09/08
01 Apr 2009

Hi,Well, to generate a sequence of n numbers with ROW_NUMBER() or other window function you need to have a table that has at least n rows which is an unnecessary assumption (how can you guarantee that it has, say, 1000000 rows?).Therefore, to generate a sequence from 0 to 99 without referencing any table, use recursive query:WITH RECURSIVE tmp ( counter) AS ( SELECT s.counter FROM (SELECT 0 AS counter) AS s -- lower limit UNION ALL SELECT t.counter + 1 AS counter FROM tmp AS t WHERE t.counter < 99 -- upper limit)SELECT t.counterFROM tmp AS t;Unfortunately, one problem with the recursion arises when the sequence has to be longer, for instance from 0 to 999999. To avoid so many recursive unions, just generate sequence of digits from 0 to 9 and use as many cross joins as you need to generate the final sequence of numbers:WITH RECURSIVE tmp ( counter) AS ( SELECT s.counter FROM (SELECT 0 AS counter) AS s -- lower limit UNION ALL SELECT t.counter + 1 AS counter FROM tmp AS t WHERE t.counter < 9 -- upper limit)SELECT t0.counter + t1.counter * 10 + t2.counter * 100 + t3.counter * 1000 + t4.counter * 10000 + t5.counter * 100000 AS final_counterFROM tmp AS t0CROSS JOIN tmp AS t1CROSS JOIN tmp AS t2CROSS JOIN tmp AS t3CROSS JOIN tmp AS t4CROSS JOIN tmp AS t5;

satishjampa 7 posts Joined 04/11
01 Jun 2011

Hi Forum,

I'm new to TD and im working on copying a table with Identity column and with DATA into another DB with the table name. I checked in the forums that cannot copy a table with Identity columns. Is there a work around . i need all the steps please.

mnylin 118 posts Joined 12/09
15 Jun 2011

You can always just use SHOW TABLE to get the definition and execute with the new table name, then insert the data. You'll have to do a few things though. First, if the identity column is set as GENERATED ALWAYS, you're probably out of luck. Because when you insert the data, the old value for that field will be ignored. If the identity column is GENERATED BY DEFAULT, you'll need to change the starting value to something higher than the highest value in the source table. Once that's done, you can just use an INSERT SELECT statement to copy the data from source to target.

You must sign in to leave a comment.