Oracle SQL: Use Sequence in Insert with Select Statement

Oracle SQL: Use sequence in insert with Select Statement

Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like

INSERT INTO HISTORICAL_CAR_STATS (
    HISTORICAL_CAR_STATS_ID, 
    YEAR,
    MONTH, 
    MAKE,
    MODEL,
    REGION,
    AVG_MSRP,
    CNT) 
SELECT MY_SEQ.nextval,
       year,
       month,
       make,
       model,
       region,
       avg_msrp,
       cnt
  FROM (SELECT '2010' year,
               '12' month,
               'ALL' make,
               'ALL' model,
               REGION,
               sum(AVG_MSRP*COUNT)/sum(COUNT) avg_msrp,
               sum(cnt) cnt
          FROM HISTORICAL_CAR_STATS
         WHERE YEAR = '2010' 
           AND MONTH = '12'
           AND MAKE != 'ALL' 
         GROUP BY REGION)

Use sequence number with insert select

Keyword distinct is incompatible with sequence querying. If you really need it, try something like

INSERT INTO mySchema.ODI_PRICELIST_THREAD_TABLE (
 src_table,
 thread_id,
 creation_date)
select 
  a.src_table,
  num_thread_seq.nextval,
  a.create_date
from
  (select distinct src_table, create_date from mySchema.nb_pricelist_ctrl) a

Insert a sequence and SELECT

INSERT INTO ORDERS(order_id, foo1, foo2, foo3)
SELECT SEQUENCE_ORDERS.nextval,foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y

Insert from select with sequence and group by

The problem here is because your sequence is not aggregated, therefore you have this error. Try this way:

insert into tableA (
  taba_id,
  taba_date,
  taba_sum
) 
 select tabb_sequence.nextval,
        tabb_date,
        stv
   from (select tabb_date,
                sum(tabb_value) stv,
           from tableB 
          group by tabb_date) a;

Using Sequence for inserting

To use a sequence to generate IDs, you create it normally in the schema where the table is.

As user application user GEM_APP:

CREATE TABLE my_table (id NUMBER, col1 ...);
CREATE SEQUENCE my_seq;

The application user itself (and f.i. it's stored procedures) can use the sequence directly:

INSERT INTO my_table (id, col1) VALUES (my_seq.nextval, 'bla');

However, other users need the correct privileges. Usually, you grant select rights on the sequence to the same users or roles you grant insert rights on the table:

GRANT SELECT, INSERT ON my_table TO user_xy;
GRANT SELECT ON my_seq TO user_xy;

Then the other user can insert data into the table, but must qualify the schema:

INSERT INTO gem_app.my_table(id, col1) VALUES (gem_app.my_seq.nextval, 'bla');

You can create aliases to hide the schemas, some people like them, some not, but I would definitely not recommend to use PUBLIC synonyms as they are hard to control and create all kind of namespace clashes.

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

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.

Oracle: Insert Rows from Select with New Sequence IDs

First create a query that will display the rows you want:

SELECT
    Id,
    col1, col2, col3, ....., colN
FROM table
WHERE /* the condition which selects rows you want */
   id >= 6 AND id <= 10

then replace ID column with TABLE_SEQUENCE.NEXTVAL and prepend the whole query with INSTERT statement:

INSERT INTO table( Id, col1, col2, col3, ....., colN )
SELECT
    TABLE_SEQUENCE.NEXTVAL,
    col1, col2, col3, ....., colN
FROM table
WHERE /* the condition which selects rows you want */
   id >= 6 AND id <= 10

Inserting multiple rows with sequence in Oracle

The restrictions on multitable inserts include:

• You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

That isn't quite true - you can use a sequence, it just always gets the same value, so it can be useful to create parent and child records in one go by referring to the same sequence.

If you want to continue to use insert all you could work around that by using a non-deterministic function that gets the sequence value:

CREATE FUNCTION get_seq RETURN NUMBER IS
BEGIN
  RETURN postal_code_seq.nextval;
END;
/

INSERT ALL
  INTO POSTAL_CODE( postal_code,description)
    VALUES(get_seq,'Coimbatore')
  INTO POSTAL_CODE (postal_code,description)
    VALUES(get_seq,'Mumbai') SELECT * FROM DUAL;

2 rows inserted.

SELECT * FROM postal_code;

                            POSTAL_CODE DESCRIPTION        
--------------------------------------- --------------------
                                      1 Coimbatore          
                                      2 Mumbai              

But that's a bit awkward. You're probably better off using individual insert statements - using a multitable insert into a single table isn't really gaining you much anyway - or a trigger to set the unique column from the sequence, or a CTE/inline view to generate the values to insert.

---

Related Topics