title: Insert unknown number of values
body: <p>I have a table <em>import_saved_format_det</em> (call it A) structured as followed:</p>
  
  <pre><code>row_id | hdr_id | column_name
  1      | 1      | supplier_name
  2      | 1      | branch_name
  3      | 2      | supplier_name
  4      | 2      | branch_name
  5      | 2      | barcode
  </code></pre>
  
  <p>The number of <em>column_name</em>'s for a given <em>hdr_id</em> is unknown but always 5 or less.</p>
  
  <p>I have another table called <em>traxs_temp..__SimpleImport_ColumnNames</em> (call it B) structured as followed:</p>
  
  <pre><code>row_id | session_id | field1 | field2 | field3 | field4 | field5
  </code></pre>
  
  <p>with field1 to field5 nullable.</p>
  
  <p>In a stored procedure I want to INSERT the <em>column_name</em>'s from A for a given <em>hdr_id</em> in the table B. My problem is that I don't know how many <em>column_name</em> I have to insert in advance.</p>
  
  <p>Here is what I did so far</p>
  
  <pre><code>DECLARE @hdr_id int = 1
  DECLARE @session_id varchar(30) = 'test'
  
  DECLARE @list varchar(max) = ''
  SELECT @list = @list + '[' + column_name + '],' FROM import_saved_format_det WHERE hdr_id = @hdr_id
  SELECT @list = REVERSE(STUFF(REVERSE(@list), 1, 1, ''))
  
  DECLARE @sql NVARCHAR(MAX)
  SET @sql = 'INSERT INTO traxs_temp..__SimpleImport_ColumnNames
                  (session_id, 
                  field1, 
                  field2, 
                  field3, 
                  field4,
                  field5)
              SELECT 
                  @session_id,
                  * 
              FROM 
                  (
                      SELECT column_name FROM import_saved_format_det WHERE hdr_id = @hdr_id
                  ) src PIVOT ( MAX(column_name) FOR column_name IN (' + @list  + ') ) piv;'
  
  EXEC SP_EXECUTESQL @sql, N'@hdr_id int, @session_id varchar(30)', @hdr_id, @session_id
  </code></pre>
  
  <p>But I'm getting <strong>The select list for the INSERT statement contains fewer items than the insert list.</strong></p>
  
  <p>Is there a way to set the remaining <em>field</em>'s to null? Or am I thinking this wrong?</p>