sql - 具有同一列的多个 INSERT (MS ACCESS)

Question

我试图从这样的表（DesignID，ink1，ink2，ink3，mesh1，mesh2，mesh3）附加到新表（ID，DesignID，ink，mesh）以减少重复值......我是对 SQL 很陌生，找不到答案...我试图弄清楚如何在一个查询中执行此操作，而不是运行 3 次...

到目前为止，我试过这个：

INSERT INTO Ink ( DesignID, Mesh, Ink )
    SELECT Design.DesignID, Design.Mesh1, Design.Ink1
    FROM Design
    WHERE (((Design.Ink1) Is Not Null))

    UNION ALL

INSERT INTO Ink ( DesignID, Mesh, Ink )
    SELECT Design.DesignID, Design.Mesh2, Design.Ink2
    FROM Design
    WHERE (((Design.Ink2) Is Not Null));

UNION ALL

INSERT INTO Ink ( DesignID, Mesh, Ink )
    SELECT Design.DesignID, Design.Mesh3, Design.Ink3
    FROM Design
    WHERE (((Design.Ink3) Is Not Null));

非常感谢任何帮助！

标签： sqlms-access

Answer 1

尝试使用 aSELECT ... INTO与两个选择的联合：

SELECT t.* INTO Ink
FROM
(
    SELECT DesignID, Mesh1, Ink1
    FROM Design
    WHERE Ink1 IS NOT NULL
    UNION ALL
    SELECT DesignID, Mesh2, Ink2
    FROM Design
    WHERE Ink2 IS NOT NULL
) t;