title: Inserting dynamic pivot result into an existing table without knowing table definition
body: <p>I have a dynamic pivot table that produces an unknown number of columns until runtime. I am trying to clear the data out of an existing table and insert the results of my query into it. The problem that I am having is that I do not know what the table definition will be ahead of time because it is dynamic. </p>
  
  <p>Here is the error I am getting: <br>
  <code>Msg 213, Level 16, State 7, Line 1
  Column name or number of supplied values does not match table definition.</code></p>
  
  <p>The pivoted column is a date column which could have 1 date or 1000+ dates.
  How can I create a table definition that will match the data that I am trying to insert?</p>
  
  <p>Here is my query: <br></p>
  
  <pre><code> DECLARE @colsPivot AS NVARCHAR(MAX),
   @colsUnpivot AS NVARCHAR(MAX),
   @query AS NVARCHAR(MAX)
  
  select @colsPivot = STUFF((SELECT ',' + QUOTENAME(rce.EcoDate) 
                               from PhdRpt.RptCaseEco_542 AS rce
                               group by rce.EcoDate
                               order by rce.EcoDate
                       FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                       ,1,1,'')
  
  SELECT @colsUnpivot = stuff((SELECT ','+quotename(C.name)
  FROM sys.columns AS C
  WHERE C.object_id = object_id('PhdRpt.RptCaseEco_542') AND
  C.name in ('NDCash', 'DCash')--LIKE '%Cash' or C.Name like  'NetGas'
  FOR xml path('')), 1, 1, '')
  
  set @query 
   = 'select *
   --into ##hello
   from
   (
       SELECT  
          ReportRunCaseId,
          col, 
          EcoDate,  
          val
      FROM PhdRpt.RptCaseEco_542  
  unpivot
  (
  val
  for col in ('+ @colsunpivot +')
  ) u
   ) x1
   pivot
   (
       max(val)
       for EcoDate in ('+ @colspivot +')
   ) p'
  
  truncate table dbo.Table1
  insert into dbo.Table1
  exec(@query)
  </code></pre>