Oracle中同表复制记录并自增ID后批量插入如何实现

现有测试表TEST结构及初始数据：

TEST(id, name, city)
      1, john, NY

需求为复制该条记录插入同表1250次，id从2自动递增到1251，以下为不同数据库的实现方案：

MySQL 实现 #

递归CTE方案（推荐，性能更高） #

INSERT INTO TEST (id, name, city)
WITH RECURSIVE seq AS (
    SELECT 2 AS id -- 起始id，比原记录id大1
    UNION ALL
    SELECT id + 1 FROM seq WHERE id < 1251 -- 共1250条，终止id为1251
)
SELECT seq.id, t.name, t.city 
FROM seq 
CROSS JOIN (SELECT name, city FROM TEST WHERE id = 1) t;

存储过程循环方案（兼容低版本MySQL） #

DELIMITER //
CREATE PROCEDURE batch_insert()
BEGIN
    DECLARE i INT DEFAULT 2;
    WHILE i <= 1251 DO
        INSERT INTO TEST (id, name, city) SELECT i, name, city FROM TEST WHERE id = 1;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用执行插入
CALL batch_insert();
-- 执行完成后可删除存储过程
DROP PROCEDURE IF EXISTS batch_insert;

PostgreSQL 实现 #

用内置generate_series函数生成序列，语法更简洁：

INSERT INTO TEST (id, name, city)
SELECT gs.id, t.name, t.city
FROM generate_series(2, 1251) gs(id)
CROSS JOIN (SELECT name, city FROM TEST WHERE id = 1) t;

SQL Server 实现 #

WITH seq AS (
    SELECT 2 AS id
    UNION ALL
    SELECT id + 1 FROM seq WHERE id < 1251
)
INSERT INTO TEST (id, name, city)
SELECT seq.id, t.name, t.city
FROM seq
CROSS JOIN (SELECT name, city FROM TEST WHERE id = 1) t
OPTION (MAXRECURSION 0); -- 关闭默认100层递归限制

注意事项 #

• 如果你的id字段本身是自增主键，不需要手动指定id插入的话，语句可以更简化，直接省略id字段即可，以MySQL为例：

INSERT INTO TEST (name, city)
WITH RECURSIVE seq AS (
    SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 1250
)
SELECT t.name, t.city FROM seq CROSS JOIN (SELECT name, city FROM TEST WHERE id =1) t;

数据库会自动给新插入的记录分配递增的id值，不需要手动计算id范围。

• 插入前建议先在测试环境验证，或者先调整序列范围做小批量测试，避免误插入多余数据。

内容的提问来源于stack exchange，提问作者Talib