CODEWITHSUNDEEP
sql-server
Madhu M
Madhu M

Reputation: 15

Insert the query result into a temp table

Insert dynamic query result in temp table

Tried with creating temp table and inserting to it

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);

result of the query to be inserted in a temp table

Upvotes: 0

Views: 89

Answers (1)

Thom A
Thom A

Reputation: 95554

The reason you are getting an error is because it's a compile error. You cannot attempt to make the same object twice (or more) within a single batch, even if only 1 (or less) of those DDL statements would be run. For example even this produces an error, despite that neither INTO could ever be be run (as 1 != 2 and 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

You need to CREATE your object first and then INSERT INTO it:

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;

Note, I have had to guess your data types; you will need to fix those.

Upvotes: 2

Related Questions