我想你的问题是

INSERT INTO GAME (game_id, name, col_3) 
SELECT seq_game_id.nextval, name, col_3
FROM GAME 
WHERE NAME = 'Texas Holdem';

生成具有新ID的新行。那么，如果你想从子表中复制行，如何找到要复制的行以及如何找到新的关联ID呢？

INSERT INTO game_detail (game_detail_id, game_id, score) 
SELECT seq_game_detail_id.nextval, ???, score
--                                 ^^^-> which ID here?
FROM game_detail
WHERE game_id in (SELECT game_id FROM game WHERE name = 'Texas Holdem')
AND ??? -- how to exclude the newly created IDs?
;

我们需要一个引用来告诉我们哪个新行源于哪个旧行。
你可以用一个pumps表来做到这一点：

CREATE TABLE copy_info AS
SELECT game_id as old_id, seq_game_id.nextval AS new_id
FROM game
WHERE name = 'Texas Holdem';

INSERT INTO game (game_id, name, col_3) 
SELECT ci.new_id, g.name, g.col_3
FROM game g
JOIN copy_info ci ON ci.old_id = g.game_id;

INSERT INTO game_detail (game_detail_id, game_id, score) 
SELECT seq_game_detail_id.nextval, ci.new_id, gd.score
FROM game_detail gd
JOIN copy_info ci ON ci.old_id = gd.game_id;

DROP TABLE copy_info;

只要没有大孩子的table，这就很好用。
另一个选择是PL/SQL：

DECLARE
  v_new_game_id         game.game_id%TYPE;
  v_new_game_detail_id  game_detail.game_detail_id%TYPE;
BEGIN
  FOR rec_game IN (SELECT * FROM game WHERE name = 'Texas Holdem') LOOP
    v_new_game_id := seq_game_id.nextval;

    INSERT INTO game (game_id, name, col_3)
    VALUES (v_new_game_id, rec_game.name, rec_game.col_3);

    FOR rec_game_detail IN (SELECT * FROM game_detail WHERE game_id = rec_game.game_id) LOOP
      v_new_game_detail_id := seq_game_detail_id.nextval;

      INSERT INTO game_detail (game_detail_id, game_id, score) 
      VALUES(v_new_game_detail_id, v_new_game_id, rec_game_detail.score;
    END LOOP;
  END LOOP;
END;

是否有编写SQL来复制整个记录集的最佳实践？
如果要从一个表中获取所有数据，用途：

SELECT * FROM table_name

或显式命名列：

SELECT column1, column2, /* ..., */ columnN FROM table_name

如果你也想INSERT数据，那么你可以使用你的查询：

INSERT INTO test_table (otherColumn1, otherColumn2, /* ... */ otherColumnN)
SELECT column1, column2, /* ..., */ columnN FROM table_name;

或者，如果存在现有数据，并且您希望跳过重复项：

MERGE INTO test_table dst
USING table_name src
ON (dst.otherColumn1 = src.column1 AND dst.otherColumn2 = src.column2)
WHEN NOT MATCHED THEN
  INSERT (otherColumn1, otherColumn2, /* ..., */ otherColumnN)
  VAUES (column1, column2, /* ..., */ columnN);

• （如果你想更新现有的行，那么你也可以使用WHEN MATCHED THEN UPDATE ...子句。

如果我有多个其他表链接到GAME怎么办？我能用一句话来实现这一点吗？
然后使用JOIN：

SELECT t1.column1,
       t1.column2,
       /* ..., */
       t1.columnN,
       t2.otherColumn1,
       t2.otherColumn2,
       /* ..., */
       t2.otherColumnN
FROM   table1 t1
       INNER JOIN table2 t2
       ON t1.column1 = t2.otherColumn1;

如果你有更多的表，那么使用更多的JOIN，如果你想INSERT的数据，那么只需要在SELECT之前前置INSERT INTO行。
如果你想MERGE与多个表，然后把查询生成的所有列你想插入到USING子句：

MERGE INTO test_table dst
USING (
  SELECT t1.column1,
         t1.column2,
         /* ..., */
         t1.columnN,
         t2.otherColumn1,
         t2.otherColumn2,
         /* ..., */
         t2.otherColumnN
  FROM   table1 t1
         INNER JOIN table2 t2
         ON t1.column1 = t2.otherColumn1
) src
ON (dst.dstColumn1 = src.column1 AND dst.dstColumn2 = src.otherColumn2)
WHEN NOT MATCHED THEN
  INSERT (dstColumn1, dstColumn2, /* ..., */ dstColumnX)
  VAUES (column1, column2, /* ..., */ otherColumnN);