CODEWITHSUNDEEP
oracle-databasesequence
Nurlan
Nurlan

Reputation: 2960

insert into with sequence oracle

I have following query in Oracle:

INSERT INTO table2 
(ID,Name,Address,MOL)
SELECT table2.ID,table2.Name,table2.Address,’sometext’
FROM table2
WHERE table2.ID=1000;

I want to implement this query when column ID have sequence called mysequence. Does someone know solution? Thanks!

Upvotes: 0

Views: 414

Answers (2)

Brian Koser
Brian Koser

Reputation: 449

Another way is to create a trigger that inserts the next sequence value:

CREATE OR REPLACE TRIGGER myschema.table2_ins_trg
   BEFORE INSERT ON myschema.table2 FOR EACH ROW
   WHEN (New.ID IS NULL) 
BEGIN
  SELECT myschema.mysequence.Nextval INTO :New.ID FROM dual;
END;

Then you leave off the ID when inserting:

INSERT INTO table2 
  (Name, Address, MOL)
SELECT table2.Name, table2.Address, 'sometext'
  FROM table2
 WHERE table2.ID = 1000;

I like this method because if I make inserts with a GUI tool, I can leave the ID blank and it will automatically be filled with the next sequence value.

Upvotes: 2

Arup Rakshit
Arup Rakshit

Reputation: 118289

INSERT INTO table2 
(ID,Name,Address,MOL)
SELECT mysequence.nextval,table2.Name,table2.Address,’sometext’
FROM table2
WHERE table2.ID=1000;

Upvotes: 4

Related Questions