Solved

need different value for each insert...

Posted on 2009-07-06
10
371 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:loginboy
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24786672
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
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 200 total points
ID: 24786928
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
 

Author Comment

by:loginboy
ID: 24787244
angelll,

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

Author Comment

by:loginboy
ID: 24787249
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24787255
>>plsql func is doing the work but with duplicate values here and there..

Where are you seeing the duplicate values?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:loginboy
ID: 24787310
the column 'a' is getting inserted with duplicate values

sample---
200907060104561046
200907060104561047
200907060104561048
200907060104561050
200907060104561050
200907060104561051
200907060104561052
200907060104561052
200907060104561053
200907060104561054
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 200 total points
ID: 24787398
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 24788334
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24788904
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24790880
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now