oracle 在 INSERT SELECT 语句期间生成增量数字列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2760563/
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
Generating incremental numeric column values during INSERT SELECT statement
提问by Igby Largeman
I need to copy some data from one table to another in Oracle, while generating incremental values for a numeric column in the new table. This is a once-only exercise with a trivial number of rows (100).
我需要在 Oracle 中将一些数据从一个表复制到另一个表，同时为新表中的数字列生成增量值。这是一个仅有一次的练习，行数很少 (100)。
I have an adequate solution to this problem but I'm curious to know if there is a more elegant way.
我对这个问题有一个足够的解决方案，但我很想知道是否有更优雅的方法。
I'm doing it with a temporary sequence, like so:
我正在使用临时序列进行操作，如下所示：
CREATE SEQUENCE temp_seq
START WITH 1;
INSERT INTO new_table (new_col, copied_col1, copied_col2)
SELECT temp_seq.NEXTVAL, o.*
FROM (SELECT old_col1, old_col2
FROM old_table,
ORDER BY old_col1) o;
DROP SEQUENCE temp_seq;
Is there way to do with without creating the sequence or any other temporary object? Specifically, can this be done with a self-contained INSERT SELECT statement?
有没有办法不用创建序列或任何其他临时对象？具体来说，这可以通过独立的 INSERT SELECT 语句来完成吗？
Please consider a trigger to be a non-option.
请考虑将触发器视为非选项。
MORE INFO:I would like to control the order that the new rows are inserted, and it won't be the same order they were created in the old table (note I've added the ORDER BY clause above). But I still want my new sequential column to start from 1.
更多信息：我想控制插入新行的顺序，它不会与它们在旧表中创建的顺序相同（注意我在上面添加了 ORDER BY 子句）。但我仍然希望我的新顺序列从 1 开始。
There are similar questions, but I believe the specifics of my question are original to SO.
有类似的问题，但我相信我的问题的细节是 SO 的原创。
回答by Peter Lang
You can use ROWNUM. This pseudo-column numbers the rows in your result:
您可以使用ROWNUM. 此伪列对结果中的行进行编号：
Insert Into new_table (new_col, copied_col1, copied_col2)
Select Rownum, old_col1, old_col2
From old_table;
If you want your records to be sorted, you need to use a sub-query:
如果要对记录进行排序，则需要使用子查询：
Insert Into new_table (new_col, copied_col1, copied_col2)
Select Rownum, old_col1, old_col2
From (
Select old_col1, old_col2
From old_table
Order By old_col1
);
回答by Philipp Andre
Why don't you define the column new_col as primary_key or unique and mark it as autoincrement? This way each insert will get the next higher "count".
为什么不将列 new_col 定义为 primary_key 或 unique 并将其标记为自动增量？这样每个插入都会获得下一个更高的“计数”。
I'm not pretty familiar with oracle, but i would bet there is a built in autoincrement function.
我对 oracle 不太熟悉，但我敢打赌有一个内置的自动增量函数。
