Solved

need different value for each insert...

Posted on 2009-07-06
10
377 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

790 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