title: Temp table not dropping when run in batch with inser
body: <p>So the below doesn't work when run.  I added the column <code>[Target_Id] INT</code> to my <code>Create Table</code> statement.  It now throws the error:  </p>
  
  <blockquote>
    <p>Msg 213, Level 16, State 1, Line 1376<br>
    Column name or number of supplied values does not match table definition.</p>
  </blockquote>
  
  <p>Here's the whole batch:</p>
  
  <pre><code>--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))
  </code></pre>
  
  <p>But if I split the batch into two parts it works.  </p>
  
  <p>Batch 1:</p>
  
  <pre><code>--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
      );
  </code></pre>
  
  <p>Batch 2:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>Anyone know why a single batch fails to drop and create the temp table, but two separate batches works?</p>