Oracle SQL, using a sequence in a separate select query

Question

I am trying to insert some data into a table from a select statement. I know I can do it like this:

insert into new_logs (idLog, logEntry)
(select idLog, logEntry from old_logs)

But, I am running into a problem when trying to execute this type of query when I need to pass in a sequence value:

insert into new_logs (idLog, logEntry)
(select LOGSEQ.NEXTVAL, logEntry from old_logs)

I think the problem is because the sequence comes from the dual table, but the query above implies that it comes from the old_logs table.

I also tried this,

insert into new_logs (idLog, logEntry)
select next_value for LOGSEQ, logEntry from old_logs

But I still can't get it to work. Could someone please tell me if what I am trying to do is possible? All I want to do is run an insert statement using a select query and a sequence on another schema.

It's always helpful to specify what error you are getting rather than hoping that we can guess correctly. Are you saying that logseq is a sequence that is owned by a user other than the one you are running the INSERT statement as? Does your user have permission to use the sequence? If so, you'd need a fully qualified object name unless you are creating synonyms for the sequence. — Justin Cave
– Justin Cave, Commented Oct 22, 2013 at 16:05
It should work. what error do you get. Sequence does not come from any table. It is an independent object. — Abhijith Nagarajan
– Abhijith Nagarajan, Commented Oct 22, 2013 at 16:05
The error said "sequence number not allowed here", but I rewrote the query from scratch and it worked. I guess what I typed here did not match what I was running, so I apologize — myworkaccount
– myworkaccount, Commented Oct 23, 2013 at 15:44

user2342436 · Accepted Answer · 2013-10-23 11:51:52Z

1

your query insert into new_logs (idLog, logEntry) (select LOGSEQ.NEXTVAL, logEntry from old_logs) might give error if u have duplicate logentry field in old_logs.

try this:-

    insert into new_logs (idLog, logEntry)
select LOGSEQ.NEXTVAL, logEntry from (select distinct logEntry  from old_logs);

answered Oct 23, 2013 at 11:51

user2342436

4823 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vincent Malgrat · Accepted Answer · 2013-10-22 16:10:04Z

0

Your query is correct, as shown below:

SQL> CREATE TABLE old_logs AS
  2     SELECT ROWNUM idLog, 'a' logEntry FROM dual CONNECT BY LEVEL <= 10;

Table created

SQL> CREATE TABLE new_logs AS
  2     SELECT * FROM old_logs WHERE 1 = 2;

Table created

SQL> CREATE SEQUENCE logseq;

Sequence created

SQL> INSERT INTO new_logs (idLog, logEntry)
  2  (SELECT LOGSEQ.NEXTVAL, logEntry FROM old_logs);

10 rows inserted

What error message exactly do you get?

answered Oct 22, 2013 at 16:10

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

1 Comment

myworkaccount Over a year ago

I rec'd the following error: "sequence number not allowed here", but I decided to rewrite the query from scratch and it worked. I guess my syntax did not match what I typed here, I apologize

Collectives™ on Stack Overflow

Oracle SQL, using a sequence in a separate select query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related