CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Paul
Paul

Reputation: 3582

Oracle SQL: Insert selected values as well as next value from sequence

I'm using Oracle Database 11g, PL/SQL and SQL developer.

This seems like something that's really simple but it doesn't seem to work like I thought...

Say I have some arbitrary mapping table, like:

+--------+--------+
| Letter | Color  |
+--------+--------+
| N      | Yellow |
+--------+--------+
| P      | Orange |
+--------+--------+
| Q      | Violet |
+--------+--------+
| A      | Green  |
+--------+--------+
| C      | Blue   |
+--------+--------+
| F      | Red    |
+--------+--------+

And I've created a blank logging table, to track changes to this mapping table over time.

Currently this logging table is empty, and I want to initialize it with data, so that it looks like this:

+-----+------+-------+--------+--------+
| RID | blah | blahh | Letter | Color  |
+-----+------+-------+--------+--------+
| 1   |      |       | N      | Yellow |
+-----+------+-------+--------+--------+
| 2   |      |       | P      | Orange |
+-----+------+-------+--------+--------+
| 3   |      |       | Q      | Violet |
+-----+------+-------+--------+--------+
| 4   |      |       | A      | Green  |
+-----+------+-------+--------+--------+
| 5   |      |       | C      | Blue   |
+-----+------+-------+--------+--------+
| 6   |      |       | F      | Red    |
+-----+------+-------+--------+--------+

I know I can do:

INSERT INTO my_logging_table(LETTER, COLOR)
SELECT letter, color FROM my_mapping_table;

but what if RID was a non-nullable value, so I needed to insert that alongside the selected color/letter values? (and I couldn't just drop the constraints, say)

Something like this gives me a ORA-00926: missing VALUES keyword

INSERT INTO my_logging_table(rid, letter, color)
mysequence.nextval, SELECT letter, color, FROM my_mapping_table;

and this gives me ORA-00947: Not enough values

insert into my_logging_table(rid, letter, color)  
values mySequence.nextval, select letter, color from my_mapping_table;

Finally this approahc gives me: ORA-02287: sequence number not allowed here:

insert into my_logging_table(rid, letter, color)  
select mySequence.nextval, letter, color from my_mapping_table;

What's the correct way to do this?

basically, how do I do an insert that comprises some selected values, and some static or sequence values?

Upvotes: 1

Views: 5216

Answers (2)

ArtBajji
ArtBajji

Reputation: 960

INSERT INTO my_logging_table(rid, letter, colour)
select row_number() over(), letter, color from my_mapping_table;

Hope this helps.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

Your third approach is correct, and works (with consistent column names):

create table my_mapping_table (letter varchar2(1), color varchar2(10));
insert into my_mapping_table (letter, color) values ('N', 'Yellow');
insert into my_mapping_table (letter, color) values ('P', 'Orange');
insert into my_mapping_table (letter, color) values ('Q', 'Violet');
insert into my_mapping_table (letter, color) values ('A', 'Green');
insert into my_mapping_table (letter, color) values ('C', 'Blue');
insert into my_mapping_table (letter, color) values ('F', 'Red');

create table my_logging_table (rid number, foo number, bar varchar2(10),
  letter varchar2(1), color varchar2(10));

create sequence mysequence;

insert into my_logging_table(rid, letter, color)  
select mySequence.nextval, letter, color from my_mapping_table; 

select * from my_logging_table;

       RID        FOO BAR        L COLOR    
---------- ---------- ---------- - ----------
         1                       N Yellow    
         2                       P Orange    
         3                       Q Violet    
         4                       A Green     
         5                       C Blue      
         6                       F Red

You would get that error if you were using the insert all syntax, which doesn't allow sequence references:

insert all into my_logging_table(rid, letter, color)  
select mySequence.nextval, letter, color from my_mapping_table;

Error report -
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

You'd also see this if your query had a group by clause, or an order by clause, or various other things; none of which you've shown.

An order by may be most likely if you tested the query separately. Applying any order to the generated ID doesn't really make any sense, but if you did want to for some reason (perhaps based on a timestamp) then you'd need to use a subquery and refer to the sequence in the outer query:

insert into my_logging_table(rid, letter, color)
select mySequence.nextval, letter, color from (
  select letter, color from my_mapping_table
  order by letter
); 

select * from my_logging_table;

       RID        FOO BAR        L COLOR    
---------- ---------- ---------- - ----------
         1                       A Green     
         2                       C Blue      
         3                       F Red       
         4                       N Yellow    
         5                       P Orange    
         6                       Q Violet

With a synthetic key that doesn't seem useful though. (And I'm not sure it's technically guaranteed that the order would be preserved; mostly a real-world issue with parallel processing).

You could also use @Artbaji's approach with an order by in the over() clause, but then you'd need to make sure your sequence was incremented past the values that generated.

Upvotes: 1

Related Questions