Insert with ascending CURRENT TIMESTAMP 1

[Darkwing99]

Hi experts,

I have to fill rows into a database with an insert-statement.
The problem is the primary (unique) key. The column is named TS_INSERT and it should be the TS, when the row was inserted.

But when I write:

INSERT INTO table1
SELECT CURRENT TIMESTAMP
     , var2
     , var3
     , var4
FROM   table2
WHERE
       ....

DB2 stops with SQL -803 because all "CURRENT TIMESTAMP" are the same.

Is there a workaround for this problem?

Regards,
Thomas

 

[BrianTyler]

I think that, to get unique timestamps you may have to insert the rows one at a time, possibly in a looping SQL procedure. Even then I am not sure if the timestamp will be updated for each iteration.

 

[juliane26]

Try this one:
db2 "insert into t1 select cast(generate_unique() as timestamp), c2 from t3"

That works for me on a workstation UDB, background is that generate_unique() is also 'just' a timstamp

 

[Darkwing99]

It works!
Thanx a lot.