在多行上插入单个序列值

发布于 2024-10-15 10:41:54 字数 752 浏览 3 评论 0原文

我正在尝试将多条记录插入到表中，但对每条记录使用相同的序列值。

这类似于：我怎样才能使用序列值将多行插入到 oracle 中？但是给出的答案会插入多个不同的序列号，并且我希望多个记录具有相同的序列号。

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;

上面的问题是它检索并插入多个（不同的）“test_seq.nextval”值，并且我希望为每一行插入相同的值。

这是否可以在直接的 sql 中而不借助触发器（或多个 sql 语句）来实现？相关问题的答案之一暗示可能不是，但我不清楚。

谢谢。
如果有帮助的话，我正在使用 Oracle 11g。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

与他有关 2024-10-22 10:41:54

使用 currval 而不是 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;

我知道没有两个语句可以做到这一点，第一个语句增加序列（从而使其可以通过 currval 选择），第二个语句使用 currval。

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.

回复收藏 0 原文

~没有更多了~