asked on

need different value for each insert...

Using a insert select statment... using systimestamp in one of the column .... want differnt timestamp for each record insert...

right now it's same for all the records as using insert select....

plz see the code...

Now there would be say 1000 rows inserting... so for each insert there should different a value i.e different time stamp ....

column 'a' is of datatype varchar2(50)

Insert into table tst1(a,b,c)
select to_char(systimestamp, 'yyyymmddhhmissff'), name,dept
from tst2
/

Open in new window

Guy Hengel [angelIII / a3]

what about this:

Insert into table tst1(a,b,c)
select to_char(systimestamp + interval rownum 'SECOND', 'yyyymmddhhmissff'), name,dept
from tst2
/

Open in new window

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

loginboy

ASKER

angelll,

Thanks for quick response.
This query is giving me error.

loginboy

ASKER

mrjoltcola:

Thanks for quick response.

plsql func is doing the work but with duplicate values here and there..
which is not allowed.

will DBMS_LOCK.SLEEP help??

Unique values are needed...

mrjoltcola

>>plsql func is doing the work but with duplicate values here and there..

Where are you seeing the duplicate values?

loginboy

ASKER

the column 'a' is getting inserted with duplicate values

sample---
200907060104561046
200907060104561047
200907060104561048
200907060104561050
200907060104561050
200907060104561051
200907060104561052
200907060104561052
200907060104561053
200907060104561054

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

why not use systimestamp and a sequence in a insert trigger ?
and then combine them

CREATE OR REPLACE TRIGGER TESTER.TRG_TST1_INS_A
BEFORE INSERT
ON TESTER.TST1 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;
 
   SELECT SEQ_A.NEXTVAL INTO tmpVar FROM dual;
   
   :NEW.A := to_char(systimestamp, 'yyyymmddhhmissff') || to_char(tmpVar, 'FM0000000000');
END TRG_TST1_INS_A;
/

Open in new window

Naveen Kumar

You are seeing the same values for all the rows with the below insert because that is the way oracle is designed to work.

Insert into table tst1(a,b,c)
select to_char(systimestamp, 'yyyymmddhhmissff'), name,dept
from tst2

This insert is a single statement and the call to systimestamp function as well will be made once to get the value and populated for all the rows accordingly. The function which we call determines what result we are going to get in the output whether the same output for all records or the output changes ( deterministic or non deterministic function )

the best idea would be to write a user defined function and call it from the above sql instead of systimestamp. I think you can even use dbms_lock.sleep etc to have a slight delay but that is goign to slow down your insert as well.

Ideally if you want something unique for one the field, why not have an extra field and populate it with a numeric sequence and leave this time stamp field as it is which will help you not to meddle with something which is not really good from design point of view. anyways, choice is yours !!

if you can pin down your requirement in a bit more details, then a common solution can be agreed which is good in all ways.