how to insert static values into table variable using select statement

Question

How to insert static values in table variable using select statement (without using multiple insert statment) in MS SQL Server.

e.g

Declare @xyz table(
abc varchar(10),
pqr varchar(10)
)
insert into @xyz(abc, pqr) values('a1', 'p1')
insert into @xyz(abc, pqr) values('a2', 'p2')
insert into @xyz(abc, pqr) values('a3', 'p3')

Instead, Is there a way to write something like below ?

insert into @xyz(abc, pqr)
select abc, pqr
from (
    'a1', 'p1',
    'a2', 'p2',
    'a3', 'p3'
)res

Thank you,

user1443098 · Accepted Answer · 2018-06-29 20:49:21Z

3

insert into @xyz(abc, pqr)
select abc, pqr
from (VALUES
    ('a1', 'p1'),
    ('a2', 'p2'),
    ('a3', 'p3')
)res(abc, pqr)

answered Jun 29, 2018 at 20:49

user1443098

7,77511 gold badges43 silver badges79 bronze badges

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

Comments

Ajay Gupta · Accepted Answer · 2018-06-29 20:48:33Z

1

insert into @xyz(abc, pqr) values('a1', 'p1'),('a2', 'p2'),('a3', 'p3')

answered Jun 29, 2018 at 20:48

Ajay Gupta

1,8651 gold badge14 silver badges22 bronze badges

Comments

S3S · Accepted Answer · 2018-06-29 20:48:35Z

1

You don't have to repeat the insert statement...

Declare @xyz table(
abc varchar(10),
pqr varchar(10)
)
insert into @xyz(abc, pqr) 
values

('a1', 'p1')
,('a2', 'p2')
,('a3', 'p3')

Or you can nest it with union all

insert into @xyz(abc, pqr) 
select abc, pqr
from
(
    select
    'a1' as abc , 'p1' as pqr
    union all
    select
    'a2', 'p2'
    union all
    select
    'a3', 'p3'
) x

Or a table constructor

insert into @xyz(abc, pqr) 
select abc, pqr
from
(values('a1','p1'),('a2', 'p2'),('a3', 'p3'))  as x(abc, pqr)

answered Jun 29, 2018 at 20:48

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

how to insert static values into table variable using select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related