Inserting a single sequence value on multiple rows

Question

I'm trying to insert multiple records into a table, but using the same sequence value for every record.

This is similiar to: How can I insert multiple rows into oracle with a sequence value? however the answer given inserts multiple, different sequence numbers, and I want the same sequence number for multiple recs.

create table test1  ( 
  col_a number, 
  col_b number
);
commit;

create sequence test_seq increment by 1 start with 2 minvalue 1 nocycle nocache noorder;
commit;

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

The problem with the above is that it retrieves and inserts multiple (different) "test_seq.nextval" values, and I want the same value inserted for every row.

Is this even possible in straight sql without resorting to a trigger (or multiple sql statements)? One of the answers to the related question hinted it may not be, but it wasn't clear to me.

Thanks.
I'm using Oracle 11g if that helps.

score 6 · Accepted Answer · edited Apr 01 '18 at 14:39

6

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.

edited Apr 01 '18 at 14:39

user2672165

2,986
19
27

answered Jan 31 '11 at 20:31

René Nyffenegger

39,402
33
158
293

Thanks. I was hoping for a single sql statement, but this isn't too bad. I knew about the"currval", but for some reason didn't think about it in this context. If nobody finds a way to do this in a single stmt, I'll accept this answer. – Gerrat Jan 31 '11 at 20:35
And I'd certainly be interested to learn about it if there is a single-statment solution. – René Nyffenegger Jan 31 '11 at 20:38
2

Why a single statement? Does putting it into a single pl/sql block count as a single statement, i.e. a single executable command? – araqnid Jan 31 '11 at 20:45
@araqnid: Haha...no *real* reason at all for wanting a single stmt. If the canonical answer is to use 2 sql stmts, then that's fine. ..it just seems like it *should* be easy to do in a single stmt (and I just can't figure it out). – Gerrat Jan 31 '11 at 20:58
@OMG: Thanks. I think it's ok though. It'll be inside a transaction, and the CURRVAL doesn't change when I call NEXTVAL from another session. I tested that (and was pleasantly surprised that it works) – Gerrat Feb 01 '11 at 00:46

Inserting a single sequence value on multiple rows

1 Answers1