title: SQL Server - Using a List parameter as columns for a subsequent insert statement
body: <p>I am trying to construct the following query:</p>
  
  <pre><code>delete from product where product_id = 'some_id'
  and supplier_id IN ('some_id1', 'some_id2')
  
  insert into product (product_id, is_active, supplier_id)
  select 'some_id' as product_id, 1 as is_active, newidst.new_ids as supplier_id
  from (select ('new_id1,new_id2') as new_ids) from newidst
  </code></pre>
  
  <p>i expected 0 rows to be deleted, and 2 rows to be inserted.
  but I only got 1row inserted. </p>
  
  <p>my expected output would be the following 2 rows)</p>
  
  <pre><code>some_id, 1, new_id1
  some_id, 1, new id2
  </code></pre>
  
  <p>What I can understand is that it's taking 'new_id1,new_id2' as a column but not as two different values as separated by comma(,). I am not sure how to do this without creating a temp table, which I don't want to. And using <code>UNION ALL</code> would be challenging since those new_id list might be quite long in some cases. The challenge here is that I am trying to update that legacy query without rewriting it too much.</p>
  
  <p>Any help or suggestion is appreciated.</p>