不能通过使用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屋！
