SQL DB2：使用选择插入，每次插入时将每个新行的列增加一？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9096541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2: Insert into with select, incrementing a column for each new row by one for each insert?
提问by Mo.
Im trying to copy the contents from a column in one table to another and at the same time want to populate the primary key column with an incrementing number for each row created:
我试图将一个表中一列的内容复制到另一个表中，同时希望为创建的每一行填充主键列的递增数字：
I have tried doing the following:
我尝试执行以下操作：
INSERT INTO Table1 (col1, col2) VALUES((SELECT col1 FROM table2), (SELECT NEXTVAL FOR col2_SEQ FROM sysibm.sysdummy1));
but get the following error:
但得到以下错误：
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0348N "NEXTVAL FOR col2_SEQ" cannot be specified in this
context. SQLSTATE=428F
It seems that i cant use the sequence value in this way, is there any other way I can achieve what I'm trying to do? I just need col2 in table1 to be populated with a unique BIGINT for each new entry from col1 from table2
似乎我不能以这种方式使用序列值，还有其他方法可以实现我想要做的事情吗？我只需要 table1 中的 col2 为 table2 中 col1 中的每个新条目填充一个唯一的 BIGINT
回答by bhamby
If you're on Linux/Unix/Windows (and probably for others), I think you just want NEXT VALUE FOR sequence. You don't need the extra select from sysdummy in this context.
如果您使用的是 Linux/Unix/Windows（可能也适用于其他人），我认为您只需要NEXT VALUE FOR sequence. 在这种情况下，您不需要从 sysdummy 中进行额外的选择。
INSERT INTO table1 (col1, col2)
SELECT col1, NEXT VALUE FOR col2_SEQ
FROM table2
回答by Deepa
There are 3 methods in which unique values can be generated in DB2.
有 3 种方法可以在 DB2 中生成唯一值。
GENERATE_UNIQUEfunctionIDENTITYcolumnSEQUENCEobject
GENERATE_UNIQUE功能IDENTITY柱子SEQUENCE目的
Assuming col2_SEQis created similar to below statement:
假设col2_SEQ创建类似于以下语句：
CREATE SEQUENCE col2_SEQ
AS INTEGER
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
ORDER
The insert statement can be written as follows:
插入语句可以写成如下：
INSERT INTO Table1 (col1, col2)
VALUES ((SELECT col1 FROM table2),
NEXT VALUE FOR col2_SEQ)
More information, on each of the three methods mentioned above, can be found here
可以在此处找到有关上述三种方法中每一种的更多信息
回答by kns
There is also alternative syntax now, which worked for me in DB2 10.x
现在还有替代语法，它在 DB2 10.x 中对我有用
INSERT INTO table1 (col1, col2)
SELECT col1, schema.seq_name.nextval
FROM table2;
回答by Peter Miehle
Maybe you should specify the columns as:
也许您应该将列指定为：
col2 smallint not null
generated by default as identity (start with 1, increment by 1)
and insert into table1 select col1, default from table2
和 insert into table1 select col1, default from table2
