复制SQL表生成大型测试表遇DECLARE语法错误，求最优方案

你的代码问题分析 #

1. 语法兼容性问题：你用的DECLARE @Counter是SQL Server/MySQL的变量声明语法，但从报错来看，你当前使用的SQL引擎（比如Hive、Spark SQL）不支持这种写法，因此触发了语法错误。
2. 逻辑错误：就算语法通过，你的循环会重复创建同名表my_new_big_table，第二次循环就会提示表已存在，根本无法实现数据累加的目的。

正确复制表结构+生成大测试表的方法 #

方法1：直接用UNION ALL重复数据（适合中小规模放大） #

如果只是想把原表数据复制N倍，不需要循环，直接用UNION ALL拼接多次查询即可：

CREATE TABLE my_new_big_table AS
SELECT * FROM my_table
UNION ALL
SELECT * FROM my_table
UNION ALL
SELECT * FROM my_table
-- 要复制10次就写10行SELECT，根据需求调整次数

方法2：递归CTE实现动态放大（适合大规模倍数放大） #

如果需要放大几十上百倍，写大量UNION ALL太麻烦，可以用递归CTE生成重复次数，再关联原表：

WITH recursive_counter AS (
    SELECT 1 AS cnt
    UNION ALL
    SELECT cnt + 1 FROM recursive_counter WHERE cnt < 10 -- 这里的10是复制次数
)
CREATE TABLE my_new_big_table AS
SELECT t.* 
FROM my_table t
CROSS JOIN recursive_counter;

该方法适合支持递归CTE的引擎（如Hive 2.1+、Spark SQL、PostgreSQL等）。

方法3：分批次插入（避免大表一次性生成的性能问题） #

如果原表数据量极大，一次性生成大表可能导致资源耗尽，可以先复制空表结构，再分批次插入：

-- 先复制表结构（不含数据）
CREATE TABLE my_new_big_table AS SELECT * FROM my_table WHERE 1=0;

-- 以Hive环境为例，用shell脚本循环插入：
-- for i in {1..10}; do
--     INSERT INTO my_new_big_table SELECT * FROM my_table;
-- done

最优方案选择 #

• 小倍数放大（10倍以内）：直接用UNION ALL最简洁高效，语法兼容性最强。
• 大倍数放大（几十上百倍）：递归CTE更简洁，大幅减少代码量。
• 超大规模数据：分批次插入更稳妥，避免一次性占用过多系统资源。

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