sql - MS Access SQL : Using SELECT INTO with a UNION ALL query

我正在尝试将一个已经工作的 UNION 查询转换为 SELECT INTO 作为一堆临时表的 VBA 过程的一部分。我这样做是因为 Access 存在性能问题。

当我尝试在插入 INTO 子句的情况下运行此查询时，出现此错误:

"An action query cannot be used as a row source."

我不完全确定为什么我不能执行它或如何在不创建更多查询的情况下绕过它。这是什么原因造成的，我该如何解决？

SELECT DISTINCT A.*
FROM (SELECT DISTINCT
C.[CUSTOMER CODE],
D.[FISCAL DAY],
D.[TY INVOICE DATE] AS [Invoice Date],
C.[CUSTOMER CODE]&" - "&D.[FISCAL DAY] AS CONCAT,
IIF(D.[TY INVOICE DATE] Is Not Null, 'TY', Null) As TYLY

***INTO [FD CUST Missing- Temp]***

FROM [QR- CUST FD] AS C,
[QR- FD SALES] AS D) AS A

LEFT JOIN [QR- CUST FD] AS C
ON C.[FD CONCAT] = A.[CONCAT]
WHERE C.[FD CONCAT] Is Null

UNION ALL

SELECT DISTINCT
A.*

***INTO [FD Cust Missing- Temp]***

FROM (SELECT DISTINCT
C.[CUSTOMER CODE],
D.[FISCAL DAY],
D.[LY INVOICE DATE] AS [Invoice Date],
C.[CUSTOMER CODE]&" - "&D.[FISCAL DAY] AS CONCAT,
IIF(D.[LY INVOICE DATE] Is Not Null, 'LY', Null) As TYLY

FROM [QR- CUST FD] AS C,
[QR- FD SALES] AS D)  AS A

LEFT JOIN [QR- CUST FD] AS C
ON C.[FD CONCAT] = A.[CONCAT]
WHERE C.[FD CONCAT] IS NULL

;

最佳答案

你收到错误



因为 SELECT ... INTO NewTableName FROM ... 查询是不返回结果集的操作查询(如 INSERT、UPDATE 或 DELETE 查询)。因此 UNION ALL 没有任何作用。因此，例如，这将不起作用(导致上述错误消息):

    SELECT DISTINCT LastName
    INTO Foo
    FROM Clients
    WHERE LastName LIKE 'D*'
UNION ALL
    SELECT DISTINCT LastName
    INTO Foo
    FROM Clients
    WHERE LastName LIKE 'T*'

但是，您可以按如下方式重新组织该查询，这确实有效:

SELECT *
INTO Foo
FROM
    (
        SELECT DISTINCT LastName
        FROM Clients
        WHERE LastName LIKE 'D*'
    UNION ALL
        SELECT DISTINCT LastName
        FROM Clients
        WHERE LastName LIKE 'T*'
    )

关于sql - MS Access SQL : Using SELECT INTO with a UNION ALL query，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30869832/