Copy the record and increment in its one column

Database
Fan

Copy the record and increment in its one column

Hi, 

So i've been searching all around on how i could increment a column in Teradata. I'm new to this so im not really familiar with all the syntax or tactics that i could use. 

I've read about autogenerated identity column, OLAP and recursive in teradata but i think it does not matches my need. 

So here's my problem.

There are existing records in my table, so lets say i have 3 records ('a','b','c','d',1,'e')  && ('f','g','h','i',1,'j')  && ('k,'l','m','n',1,'o') 

then i need to replicate these records, and increment the only one integer column ('a','b','c','d',2,'e') && ('f','g','h','i',2,'j') &&  ('k,'l','m','n',2,'o')   ~

i need to repeat this until the max incrementation of integer (which is 15) is reached...

I have like miliion of records to update, and manual insertion would really help at this moment. 

Any advise would really help.

3 REPLIES
Fan

Re: Copy the record and increment in its one column

So, i though of insert select and putting up the integer value as null....then update it via

SEL CSUM(1,1) + 1 AS SEQ

FROM SYS_CALENDAR.CALENDAR

QUALIFY SEQ < 16

However, i have to copy each record 15 times... and i have millions of it..

Junior Contributor

Re: Copy the record and increment in its one column

You are close :-)

Cross join to a list of numbers between 1 and 15:

insert into tab
select tab.col1,tab.col2,tab.colx + n
from tab cross join
( -- get numbers from 1 to 15
select top 15 day_of_calendar as n
from sys_calendar.calendar
order by day_of_calendar
) as dt
Fan

Re: Copy the record and increment in its one column

Hi Dieter!!

Im a fan of yours, ive been reading most of your posts here and it really helped me a lot to improve my teradata sql skills.. 

by the way, i have tried it and it worked!! thank you and God!