Insert multiple rows in a single query using results of a select statement

Question

I am looking for a compact way to do this - insert multiple rows into a table with values from multiple columns of a row from another table. My destination table is really a list with a single column:

declare @stringList table
(
    val nvarchar(100)
)

This is how we can insert multiple rows:

INSERT INTO @stringList ( val ) VALUES
Val1, Val2, Val3, ...

This is how we insert from a select:

INSERT INTO @stringList 
SELECT col1 FROM table1 where id=something

But I cannot seem to find a way to use both at the same time.

I can select from one column:

insert into @stringList (val) 
select col1 from table1 where id=something

But it doesn't extend to multiple columns:

insert into @stringList (val) 
select col1, col2 from table1 where id=something

--The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

I have tried various ways including using parentheses, but the syntax is not accepted:

insert into @stringList (val) 
(select col1 from table1 where id=something,
select  col2 from table1 where id=something

Any idea if what I want is doable?

You can use a cursor to get row level data from one table and insert into another table by using a loop. — user3315556, Nov 19 '20 at 17:50

Gordon Linoff · Accepted Answer · 2020-11-19T17:50:47.193

3

You can unpivot using cross apply:

insert into @stringList (val) 
    select v.col
    from table1 t1 cross apply
         (values (t1.col1), (t1.col2)) v(col)
    where t1.id = something;

edited Nov 19 '20 at 17:50

answered Nov 19 '20 at 17:43

Gordon Linoff

1,242,037
58
646
786

1

My mind is blown. I did not know you could use *values* that way. So many wasted unions... – Daniel Gimenez Nov 19 '20 at 18:28
1

@DanielGimenez . . . This is also more efficient. – Gordon Linoff Nov 19 '20 at 19:34

Insert multiple rows in a single query using results of a select statement

1 Answers1