Oracle中使用CTE通过COPY命令跨模式复制数据报错求助

尝试在同一数据库的不同模式下，使用包含CTE（公共表表达式）的COPY命令将数据从一张表复制到另一张表，执行的SQL语句如下：

COPY FROM my_schema/password -
INSERT PRODUCT -
USING 
WITH cte AS (
    SELECT
    p.id,
    p.vendor,
    p.name,
    p.product_alias,
    p.platform
    FROM
        memuat.product p
        JOIN memuat.licence_management l 
        ON p.id = l.product_id
), 
joined as (
SELECT
    cte.*,
    ROW_NUMBER() OVER (PARTITION BY vendor,name ORDER BY vendor,name ) as rn
    from cte 
) 
select ID,VENDOR,NAME,PLATFORM,PRODUCT_ALIAS from joined where rn =1;

执行时提示错误：

SQL statement to execute cannot be empty or null
>>Query Run In:Query Result 7

猜测是CTE生成的临时表在数据库中不存在，导致COPY命令无法复制数据，想知道是否有办法使用CTE实现此类数据复制操作。

核心问题是误用了COPY命令的语法：COPY命令的设计初衷是在数据库与外部文件之间传输数据，而非直接在数据库内的表或CTE间复制数据。要实现用CTE筛选后将数据插入目标表，应使用INSERT ... SELECT语法，而非COPY。

推荐写法（通用型） #

假设目标表为my_schema.PRODUCT，调整后的SQL如下：

INSERT INTO my_schema.PRODUCT (ID, VENDOR, NAME, PLATFORM, PRODUCT_ALIAS)
WITH cte AS (
    SELECT
        p.id,
        p.vendor,
        p.name,
        p.product_alias,
        p.platform
    FROM
        memuat.product p
        JOIN memuat.licence_management l 
        ON p.id = l.product_id
), 
joined as (
    SELECT
        cte.*,
        ROW_NUMBER() OVER (PARTITION BY vendor,name ORDER BY vendor,name ) as rn
    FROM cte 
) 
SELECT ID, VENDOR, NAME, PLATFORM, PRODUCT_ALIAS 
FROM joined 
WHERE rn = 1;

若需使用COPY（特定数据库支持） #

如果你的数据库支持从子查询/CTE复制数据到表（比如PostgreSQL 12及以上版本），则需修正COPY的语法结构，将CTE作为数据源嵌入：

COPY my_schema.PRODUCT (ID, VENDOR, NAME, PLATFORM, PRODUCT_ALIAS)
FROM (
    WITH cte AS (
        SELECT
            p.id,
            p.vendor,
            p.name,
            p.product_alias,
            p.platform
        FROM
            memuat.product p
            JOIN memuat.licence_management l 
            ON p.id = l.product_id
    ), 
    joined as (
        SELECT
            cte.*,
            ROW_NUMBER() OVER (PARTITION BY vendor,name ORDER BY vendor,name ) as rn
        FROM cte 
    ) 
    SELECT ID, VENDOR, NAME, PLATFORM, PRODUCT_ALIAS 
    FROM joined 
    WHERE rn = 1
) AS source_data;

注意：不同数据库的COPY语法差异较大，请根据实际使用的数据库进行调整。

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