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
– user3315556, Commented Nov 19, 2020 at 17:50

Gordon Linoff · Accepted Answer · 2020-11-19 17:50:47Z

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, 2020 at 17:50

answered Nov 19, 2020 at 17:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Gimenez Over a year ago

My mind is blown. I did not know you could use values that way. So many wasted unions...

Gordon Linoff Over a year ago

@DanielGimenez . . . This is also more efficient.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related