将查询结果插入临时表中。

Question

在临时表中插入动态查询结果

尝试创建临时表并将其插入

DECLARE @Currentday AS int;

SET @Currentday = CAST(REPLACE(CAST(GETDATE() AS date), '-', '') AS int);

IF @Currentday BETWEEN CAST(REPLACE(CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS date), '-', '') AS int) AND CAST(REPLACE(CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 1) AS date), '-', '') AS int)
    (SELECT ASAT,
            CIF,
            SUM(ACCOUNT_BALANCE) AS ACCOUNT_BALANCE
     INTO #SKST1
     FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
     WHERE CIF IN (SELECT CIF
                   FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
                   WHERE SPENDERTYPE = 'Revolver'
                     AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 4)
                     AND ACCOUNT_BALANCE > 0
                   GROUP BY CIF
                   HAVING COUNT(CIF) = 3)
       AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 4)
     GROUP BY ASAT,
              CIF);
ELSE
    (SELECT ASAT,
            CIF,
            SUM(ACCOUNT_BALANCE) AS ACCOUNT_BALANCE
     INTO ##SKST1
     FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
     WHERE CIF IN (SELECT CIF
                   FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
                   WHERE SPENDERTYPE = 'Revolver'
                     AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 1)
                     AND ACCOUNT_BALANCE > 0
                   GROUP BY CIF
                   HAVING COUNT(CIF) = 3)
       AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 1)
     GROUP BY ASAT,
              CIF);

要插入临时表中的查询的结果。

Answer 1

您获得错误的原因是因为它是编译错误。您不能在单个批处理中尝试两次(或更多)创建同一个对象，即使只运行1条(或更少)这些DDL语句。例如，即使这样也会产生一个错误，尽管任何一个INTO都不能运行(如1 != 2和2 != 3)：

IF 1 = 2 BEGIN

    SELECT 1 AS one
    INTO #t;

END ELSE IF 2 = 3 BEGIN

    SELECT 2 AS one
    INTO #t;
END

您需要首先对对象进行CREATE，然后INSERT INTO：

DECLARE @Currentday AS int;

SET @Currentday = CAST(REPLACE(CAST(GETDATE() AS date), '-', '') AS int);

CREATE TABLE #SKST1 (ASAT int,        --Guessed data type                
                     CIF varchar(20), --Guessed data type                
                     ACCOUNT_BALANCE decimal(12, 2)); --Guessed data type                

IF @Currentday BETWEEN CAST(REPLACE(CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS date), '-', '') AS int) AND CAST(REPLACE(CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 1) AS date), '-', '') AS int)
BEGIN

    INSERT INTO #SKST1 (ASAT,
                        CIF,
                        ACCOUNT_BALANCE)
    SELECT ASAT,
           CIF,
           SUM(ACCOUNT_BALANCE) AS ACCOUNT_BALANCE
    FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
    WHERE CIF IN (SELECT CIF
                  FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
                  WHERE SPENDERTYPE = 'Revolver'
                    AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 4)
                    AND ACCOUNT_BALANCE > 0
                  GROUP BY CIF
                  HAVING COUNT(CIF) = 3)
      AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 4)
    GROUP BY ASAT,
             CIF;

END;
ELSE BEGIN

    INSERT INTO #SKST1 (ASAT,
                        CIF,
                        ACCOUNT_BALANCE)
    SELECT ASAT,
           CIF,
           SUM(ACCOUNT_BALANCE) AS ACCOUNT_BALANCE
    FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
    WHERE CIF IN (SELECT CIF
                  FROM [COBG-BIU].dbo.MD_CARD_ENR_FAB
                  WHERE SPENDERTYPE = 'Revolver'
                    AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 1)
                    AND ACCOUNT_BALANCE > 0
                  GROUP BY CIF
                  HAVING COUNT(CIF) = 3)
      AND ASAT IN (CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 3, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 2, CAST(CONVERT(varchar(6), GETDATE(), 112) AS int) - 1)
    GROUP BY ASAT,
             CIF;

END;

注意，我不得不猜测您的数据类型；您需要修复这些类型。