CODEWITHSUNDEEP
sqlt-sqlinsertsql-server-2016
ltree
ltree

Reputation: 53

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

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?

Upvotes: 0

Views: 4108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 3

Related Questions