TimeStamp value for every record inserted using ins/select

Database
Enthusiast

TimeStamp value for every record inserted using ins/select

Hi,

i need to add a default timestamp for every row inserted into a table.

Sql Statement:

create table tbl_A
(
a integer,
b timestamp(0) default current_timestamp(0)
);

ins tbl_A(1,);

This works and the column "B" will have the timestamp of the record inserted.

However if i have to get the same from insert/select How do i get?
Ex:

insert into tbl_A
sel
a
from tbl_B;

/* would throw me an error : The position assignment list has too few values */

Please help
7 REPLIES
Enthusiast

Re: TimeStamp value for every record inserted using ins/select

2nd Try
Junior Contributor

Re: TimeStamp value for every record inserted using ins/select

You have to add a column list:

insert into tbl_A (a)
sel
a
from tbl_B;

Dieter
Enthusiast

Re: TimeStamp value for every record inserted using ins/select

Hi Dieter,

What i am trying is to get a unique timestamp for every record inserted using insert/select... However when i execute the above query for all the rows the timestamp is the same ..
Junior Supporter

Re: TimeStamp value for every record inserted using ins/select

Grao:

If you need unique values for the inserted rows you must use an IDENTITY COLUMN, not a TIMESTAMP.
No one can be sure that there will be a unique inserted row per timestamp (even if this timestamp is TIMESTAMP(6)). Keep in mind the 'legendary Teradata performance' ;-) and the paralelism...

HTH.

Cheers.

Carlos.
Junior Contributor

Re: TimeStamp value for every record inserted using ins/select

Hi Grao,
within a request the value of current_timestamp never changes, even if it runs for hours, this is Standard SQL.

If you want a unique timestamp(0) for each row you could write a Stored Procedure using a cursor with a "wait one second" UDF followed by a single row insert ;-)

I don't know why you actually want that but using a timestamp(6) you could do this:

insert into tbl_A
sel
a, current_timestamp + row_number() over (order by a) * interval '0000 00:00:00.000001' day to second
from tbl_B;

Dieter
Enthusiast

Re: TimeStamp value for every record inserted using ins/select

Hi Dieter,

Thanks very much!! Worked perfect

Junior Supporter

Re: TimeStamp value for every record inserted using ins/select

Dieter:

I think I don't get it.

The SP solution will serialize the inserts and is not guaranteed to work as expected (unique timestamp(0)) in a multiuser environment (at least without explicit locking).

The second solution may fail in several scenarios (f.ex.: the insert is executed twice and the total elapsed time of the insertion is less than the product of number of rows * 0.000001 seconds).

I don't think you can get 'bulletproof' unique values with TIMESTAMP.

Cheers.

Carlos.