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

loginboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
mrjoltcolaCommented:
a3, that syntax does not work for me. I am not that strong with the interval / analytical syntax so I cannot tell what is wrong.

Attached below PL/SQL function that will guarantee granularity of the time. It will slow down the operation a bit, but if you want granular time, you must slow it down.

Use it like this:

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

CREATE OR REPLACE FUNCTION getuniquetime RETURN TIMESTAMP
AS
  t1 timestamp;
  t2 timestamp;
BEGIN
  t1 := systimestamp;
  LOOP
    t2 := systimestamp;
    if t1 <> t2 then
       return t2;
    end if;
  END LOOP;
  return t2; 
END;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
loginboyAuthor Commented:
angelll,

Thanks for quick response.
This query is giving me error.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

loginboyAuthor Commented:
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...
0
mrjoltcolaCommented:
>>plsql func is doing the work but with duplicate values here and there..

Where are you seeing the duplicate values?
0
loginboyAuthor Commented:
the column 'a' is getting inserted with duplicate values

sample---
200907060104561046
200907060104561047
200907060104561048
200907060104561050
200907060104561050
200907060104561051
200907060104561052
200907060104561052
200907060104561053
200907060104561054
0
mrjoltcolaCommented:
Please show your SQL statement. I tested it on 100,000 rows here and there are no duplicates. Perhaps the format specifier for TO_CHAR() is wrong.
0
awking00Information Technology SpecialistCommented:
It looks like you are using a format of 'yyyymmddhhmissff4' and not 'yyyymmddhhmissff' as mrjoltcola indicated, which will give you five more characters (i.e. seconds to nine decimal places). Also, on the very off chance you might run at precisely the same time in the morning and afternoon you should use the 'yyyymmddhh24missff' format.
0
Geert GOracle dbaCommented:
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

0
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.