Solved

need different value for each insert...

Posted on 2009-07-06
10
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 32

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 37

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 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