Temp table not dropping when run in batch with inser

Question

So the below doesn't work when run. I added the column [Target_Id] INT to my Create Table statement. It now throws the error:

Msg 213, Level 16, State 1, Line 1376
Column name or number of supplied values does not match table definition.

Here's the whole batch:

--FactSite
IF OBJECT_ID('tempdb..#factSite', 'U') IS NOT NULL
    DROP TABLE #factSite;

CREATE TABLE #factSite
(
    [ID] INT,
    [Target_Id] INT,
    [SYS_CHANGE_OPERATION] NVARCHAR(1),
    DimSite_Id INT,
    DimSubSite_Id INT,
    DimSubParameters_Id INT
);

WITH cteUpivotFactSite AS
(
    SELECT [ID], [SYS_CHANGE_OPERATION], [SiteID], [SubSiteID], 
        [SubParameterVal] AS [SubParameter]
    FROM Staging.AppsFlyerBasic
    CROSS APPLY (
        VALUES ('SubParam1',[SubParam1]),
            ('SubParam2',[SubParam2]),
            ('SubParam3',[SubParam3]),
            ('SubParam4',[SubParam4]),
            ('SubParam5',[SubParam5])
    ) x ([DimensionVal],[SubParameterVal])
)
INSERT INTO #factSite
SELECT src.[ID], NULL, src.[SYS_CHANGE_OPERATION], 
    site.[ID] AS DimSite_Id, subSite.ID AS DimSubSite_Id,
    subParameters.[ID] AS DimParameters_Id
FROM cteUpivotFactSite AS src
INNER JOIN AppsFlyer.DimSite AS site
ON (src.[SiteID] = site.[Name] OR (src.[SiteID] IS NULL AND site.[Name] IS NULL))
INNER JOIN AppsFlyer.DimSubSite AS subSite
ON (src.[SubSiteID] = subSite.[Name] OR (src.[SubSiteID] IS NULL AND subSite.[Name] IS NULL))
INNER JOIN AppsFlyer.DimSubParameters AS subParameters
ON (src.[SubParameter] = subParameters.[Name] OR (src.[SubParameter] IS NULL AND subParameters.[Name] IS NULL))

But if I split the batch into two parts it works.

Batch 1:

--FactSite
    IF OBJECT_ID('tempdb..#factSite', 'U') IS NOT NULL
        DROP TABLE #factSite;
    CREATE TABLE #factSite
    (
        [ID] INT,
        [Target_Id] INT,
        [SYS_CHANGE_OPERATION] NVARCHAR(1),
        DimSite_Id INT,
        DimSubSite_Id INT,
        DimSubParameters_Id INT
    );

Batch 2:

WITH cteUpivotFactSite AS
    (
        SELECT [ID], [SYS_CHANGE_OPERATION], [SiteID], [SubSiteID], 
            [SubParameterVal] AS [SubParameter]
        FROM Staging.AppsFlyerBasic
        CROSS APPLY (
            VALUES ('SubParam1',[SubParam1]),
                ('SubParam2',[SubParam2]),
                ('SubParam3',[SubParam3]),
                ('SubParam4',[SubParam4]),
                ('SubParam5',[SubParam5])
        ) x ([DimensionVal],[SubParameterVal])
    )
    INSERT INTO #factSite
    SELECT src.[ID], NULL, src.[SYS_CHANGE_OPERATION], 
        site.[ID] AS DimSite_Id, subSite.ID AS DimSubSite_Id,
        subParameters.[ID] AS DimParameters_Id
    FROM cteUpivotFactSite AS src
    INNER JOIN AppsFlyer.DimSite AS site
    ON (src.[SiteID] = site.[Name] OR (src.[SiteID] IS NULL AND site.[Name] IS NULL))
    INNER JOIN AppsFlyer.DimSubSite AS subSite
    ON (src.[SubSiteID] = subSite.[Name] OR (src.[SubSiteID] IS NULL AND subSite.[Name] IS NULL))
    INNER JOIN AppsFlyer.DimSubParameters AS subParameters
    ON (src.[SubParameter] = subParameters.[Name] OR (src.[SubParameter] IS NULL AND subParameters.[Name] IS NULL))

Anyone know why a single batch fails to drop and create the temp table, but two separate batches works?

Answer 1

This is because the column you added, Target_Id, is not yet part of the table definition when the temp table DDL is in the same batch, and therefore this metadata is not sensed. When the table is created in a prior batch, the column you added is detected before the second batch is executed. To use columns that have not yet, but will be, added to an object in the same batch you must use dynamic SQL for accessing the table after the modifications are done as follows.

--FactSite
    IF OBJECT_ID('tempdb..#factSite', 'U') IS NOT NULL
        DROP TABLE #factSite;
    CREATE TABLE #factSite
    (
        [ID] INT,
        [Target_Id] INT,
        [SYS_CHANGE_OPERATION] NVARCHAR(1),
        DimSite_Id INT,
        DimSubSite_Id INT,
        DimSubParameters_Id INT
    );

declare @query nvarchar(2000)

set @query = N'
WITH cteUpivotFactSite AS
(
    SELECT [ID], [SYS_CHANGE_OPERATION], [SiteID], [SubSiteID], 
        [SubParameterVal] AS [SubParameter]
    FROM Staging.AppsFlyerBasic
    CROSS APPLY (
        VALUES (''SubParam1'',[SubParam1]),
            (''SubParam2'',[SubParam2]),
            (''SubParam3'',[SubParam3]),
            (''SubParam4'',[SubParam4]),
            (''SubParam5'',[SubParam5])
    ) x ([DimensionVal],[SubParameterVal])
)
INSERT INTO #factSite
SELECT src.[ID], NULL, src.[SYS_CHANGE_OPERATION], 
    site.[ID] AS DimSite_Id, subSite.ID AS DimSubSite_Id,
    subParameters.[ID] AS DimParameters_Id
FROM cteUpivotFactSite AS src
INNER JOIN AppsFlyer.DimSite AS site
ON (src.[SiteID] = site.[Name] OR (src.[SiteID] IS NULL AND site.[Name] IS NULL))
INNER JOIN AppsFlyer.DimSubSite AS subSite
ON (src.[SubSiteID] = subSite.[Name] OR (src.[SubSiteID] IS NULL AND subSite.[Name] IS NULL))
INNER JOIN AppsFlyer.DimSubParameters AS subParameters
ON (src.[SubParameter] = subParameters.[Name] OR (src.[SubParameter] IS NULL AND subParameters.[Name] IS NULL)) '

exec sp_executesql @query