项目场景:
oracle 数据库,某个表中发现缺失了一批数据,需要补全近半年的。
由于是固定的数据,只是日期差异,决定批量insert
—
表中使用了序列自动生成ID
问题描述
以下方法都试过,都报错
方法1:
INSERT ALL
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 1’)
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 2’)
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 3’)
SELECT * FROM dual;
此方法不行,会报错
方法2:
INSERT INTO my_table (id, data_column)
select id, data_column from (
SELECT my_sequence.NEXTVAL as id, ‘Data 1’ as data_column FROM dual
UNION
SELECT my_sequence.NEXTVAL as id, ‘Data 2’ as data_column FROM dual
UNION
SELECT my_sequence.NEXTVAL as id, ‘Data 3’ as data_column FROM dual
) ;
报错:ORA-02287:sequence number not allowed here
原因分析:
ORA-02287:sequence number not allowed here
ORA-02287:此处不允许有序列号
查了资料发现:需要把my_sequence.NEXTVAL提到UNION的外层
解决方案:
把生成序列提出来,不在UNION那层,便成功自动生成ID
INSERT INTO my_table (id, data_column)
select my_sequence.NEXTVAL as id, dt.*
from (
SELECT ‘Data 1’ as data_column FROM dual
UNION
SELECT ‘Data 2’ as data_column FROM dual
UNION
SELECT ‘Data 3’ as data_column FROM dual
) dt