How to Insert Multiple Rows into Oracle with a Sequence Value

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.

How can I insert multiple rows into oracle with a sequence value?

This works:

insert into TABLE_NAME (COL1,COL2)
select my_seq.nextval, a
from
(SELECT 'SOME VALUE' as a FROM DUAL
 UNION ALL
 SELECT 'ANOTHER VALUE' FROM DUAL)

Insert multiple rows with multiple values nextval Oracle SQL

Try this:

    insert into x
    select tt.* , test_id_seq.nextval from
    (select 12345, 'text' from dual
    union all
    select 23589, 'other text' from dual) tt;

Inserting a single sequence value on multiple rows

use currval instead of nextval.

select test_seq.nextval from dual;

insert into test1 (col_a, col_b)
select a.object_id, test_seq.currval from (
  select object_id from all_objects where rownum < 5
  ) a;

I know of no method to do that without two statements, the first to increment the sequence (and thus make it selectable through currval) and the second to use currval.

insert multiple row into table using select however table has primery key in oracle SQL

You're saying that your query inserts rows with primary key ID based on a sequence. Yet, in your insert/select there is select (SELECT MAX (id) + 1 FROM test) as id, which clearly is not based on sequence. It may be the case that you are not using the term "sequence" in the usual, Oracle way.

Anyway, there are two options for you ...

1. Create a sequence, e.g. seq_test_id with the starting value of select max(id) from test and use it (i.e. seq_test_id.nextval) in your query instead of the select max(id)+1 from test.
2. Fix the actual subselect to nvl((select max(id) from test),0)+rownum instead of (select max(id)+1 from test).

Please note, however, that the option 2 (as well as your original solution) will cause you huge troubles whenever your code runs in multiple concurrent database sessions. So, option 1 is strongly recommended.

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

Insert multiple rows with single a query from one table into another in Oracle

To insert all records from OLD table to the new table with new primary keys use following statement.

Note that the key part is to get the maximal existing key and increates it using ROWNUM.
For a signle migration step (i.e. no parallel migration and the application is down) this is a secure way.

 INSERT INTO dcr_details_new
 (DCRDID,TWNCODE,DOCREGNO,DOCCATOGARY,DCR_NO,VISIT_NO,GIVEAWAY,
 COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI)
 select 
  (select max(DCRDID) from dcr_details_new) + rownum as DCRDID,
  TWNCODE,DOCREGNO,DOCCATOGARY,DCR_NO,VISIT_NO,GIVEAWAY,
  COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI
 from dcr_details_old where DOCREGNO = 'T10037'
 ;

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

insert multiple rows from select statement in sql

I assume the rdbms is Oracle. You might want to create a table (e.g. "tablemapping") with these values

 FieldFrom FieldTo
 A         Aa
 B         Bb
 both      Aa
 both      Bb

So you could do just:

 Insert into tableB
 select sequence1.nextval, ID, FieldTo
    FROM tableA a 
         join tablemapping m 
           on a.type=m.fieldFrom

If you don't want to have a mapping table you can simulate one.

 Insert into tableb
 select sequence1.nextval, ID, FieldTo
    FROM tableA a 
         join (
               select 'both'  as FieldFrom,  'Ab'  as FieldTo from dual
                Union all
               select 'both'  as FieldFrom,  'Bb'  as FieldTo from dual
                Union all
               select 'A'  as FieldFrom,  'Aa'  as FieldTo from dual
                Union all
               select 'B'  as FieldFrom,  'Bb'  as FieldTo from dual
              ) tablemapping m 
           on a.type=m.fieldFrom

---

Related Topics