不能通过使用UNION ALL并从序列中生成ID来将多个值插入到DB2中 [英] Can't insert multiple values into DB2 by using UNION ALL and generate IDs from sequence
问题描述
我通过以下语句创建了序列:
I've created sequence by following statement:
CREATE SEQUENCE MAIN.MY_SEQUENCE START WITH 1 INCREMENT BY 1 CACHE 50;
并通过以下语句表:
CREATE TABLE MAIN.EMPLOYEES(
ID INTEGER NOT NULL,
NAME VARCHAR(512),
EMAIL VARCHAR(254),
PRIMARY KEY (ID)
)
现在,当我尝试插入新的记录通过使用以下语句:
Now when I try to insert a new record by using following statement:
INSERT INTO MAIN EMPLOYEES (ID, NAME, EMAIL)
VALUES (MAIN.MY_SEQUENCE.NEXTVAL, 'Name 1', 'email1@example.com') UNION ALL
VALUES (MAIN.MY_SEQUENCE.NEXTVAL, 'Name 2', 'email2@example.com')
我收到错误:
"NEXTVAL FOR MAIN.MY_SEQUENCE.NEXTVAL" cannot be specified in this context.. SQLCODE=-348, SQLSTATE=428F9, DRIVER=4.17.30
当我尝试插入一行时,一切都正常。
When I try to insert a single row everything works fine.
我发现使用 NEXT VALUE
这里,但是这里没有提到我的情况,或者我找不到。
I have found a list of restrictions on using NEXT VALUE
here but here not mentioned my case or I couldn't find it.
我的问题是可以使用序列中的ID插入多个行,如果是,是否可以实现它?
My question is it possible to insert multiple rows by using ID from sequence, and if yes, how can I achieve it?
推荐答案
它列出你的情况。文档包含以下内容:
It does list your case. The documentation contains this:
下列上下文中不能指定NEXT VALUE表达式:
... < br>
•使用set operator (例如 UNION,EXCEPT或INTERSECT)将外部SELECT与其他SELECT语句组合的SELECT语句
....
The NEXT VALUE expressions cannot be specified in the following contexts:
...
•SELECT statement for which the outer SELECT is combined with another SELECT statement using a set operator such as UNION, EXCEPT, or INTERSECT
....
(强调我的)这个说法并不详尽,因为 UNION ALL
被认为是一个设置的操作,操作被排除。
(emphasis mine) This statement isn't exhaustive, and because UNION ALL
is considered a set operation, the operation is excluded.
这应该是可修复的 - 我有点惊讶,你写的声明你的方式; DB2允许您以逗号分隔数据行。也就是说,以下内容应该是有效的:
This should be fixable - I'm a little surprised you wrote the statement the way you did; DB2 allows you to comma-separate data rows. That is, the following should be valid:
INSERT INTO MAIN.EMPLOYEES (ID, NAME, EMAIL)
VALUES (MAIN.MY_SEQUENCE.NEXTVAL, 'Name 1', 'email1@example.com'),
(MAIN.MY_SEQUENCE.NEXTVAL, 'Name 2', 'email2@example.com')
这篇关于不能通过使用UNION ALL并从序列中生成ID来将多个值插入到DB2中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!