同一列的多个INSERT（MS ACCESS）

Stan V 发表于 Dev

V公寓

我试图将像这样的表（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));

任何帮助深表感谢！

V公寓

只是对于正在寻找答案的人要明确。这是正确的查询。如@Lee_Mac所述，外加一些添加：

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT * FROM
(
SELECT DesignID, Mesh1 as Mesh, Ink1 as Ink
FROM Design
WHERE Ink1 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh2, Ink2
FROM Design
WHERE Ink2 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh3, Ink3
FROM Design
WHERE Ink3 IS NOT NULL
)

这将正常工作并没有问题。为了运行和工作，应该选择“第一选择”作为要插入的字段的名称。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。