Assign multiple values to Table variable in SQL

Question

DECLARE @ID INT
SET @ID = (select top 1 USER_REQ_JOB_ID 
           from T8504_USER_REQ_JOB 
           where JOB_GRP_ID = 160 
           order by LST_UPDT_TS desc)

SELECT INPUT_PARM_VAL_TX 
from TBL_RPT_JOB_INPUT_PARAM 
where USER_REQ_JOB_ID = @ID

This returns these results:

USA
USCC
6
7
2

These five records what I get I want to assign to five different variables to use in stored procedure.

I was trying with table variable like this :

declare @CID table (
  Region Char(3)
  ,Segment Char(3)
  ,MasterContractId int
  ,ctcid int
  ,templateid int)

insert into @CID (Region,Segment,MasterContractId,ctcid,templateid)

But how to insert that 5 rows here?

Can you show the exact query, about how you are getting those 5 values? — Rahul
– Rahul, Commented Sep 11, 2014 at 18:30
so input_parm_val_tx represents all of the values you want to assign in your table variable? How do you make the distiction if input_parm_val_tx is a region or a segment? — Kritner
– Kritner, Commented Sep 11, 2014 at 18:34
yes input_parm_val_tx represents all values...First is Regio, then segment...and so on...this order is fixed.. — Bokambo
– Bokambo, Commented Sep 11, 2014 at 18:36
I don't think there's an easy way, you can probably try pivoting; not sure though otherwise you will have to hardcode the conditions. — Rahul
– Rahul, Commented Sep 11, 2014 at 18:38

Community · Accepted Answer · 2017-05-23 12:21:40Z

1

INSERT INTO @CID
select * from
(
   select 
   'Temp' + convert(char(1), row_number() over (order by (select 0)))  as columnName,
   INPUT_PARM_VAL_TX as Value
   from TBL_RPT_JOB_INPUT_PARAM where USER_REQ_JOB_ID = @ID
) d
pivot
(
  max(value)
  for columnname in (Temp1, Temp2, Temp3, Temp4, Temp5)
) piv;

See if this helps. Take a look at this fiddle for an example.

Courtesy:
Add row number to this T-SQL query

Efficiently convert rows to columns in sql server

EDIT: The sql adds an extra column to generate row numbers to use it as an extra column, which is pivoted as column heading.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Sep 11, 2014 at 18:50

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

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

Comments

Kritner · Accepted Answer · 2014-09-11 18:46:52Z

0

it's really gross, but one way you could probably do it is this (though you'll need to apply it to your case):

http://sqlfiddle.com/#!6/d41d8/21507

declare @table TABLE (value varchar(50))
INSERT INTO @table
VALUES ('first')

INSERT INTO @table
VALUES ('second')

INSERT INTO @table
VALUES (3)

INSERT INTO @table
VALUES (4)

DECLARE @temp TABLE (id int identity(1,1), value varchar(50))
INSERT INTO @temp
SELECT [value]
FROM @table t

SELECT  *
FROM    @temp

DECLARE @CID TABLE (Region varchar(50), cont varchar(50), another int, andAnother int)

INSERT INTO @CID
(
    Region,
    cont,
    another,
    andAnother
)
VALUES
(
    (SELECT value FROM @temp WHERE id = 1), -- Region - varchar
    (SELECT value FROM @temp WHERE id = 2), -- cont - varchar
    (SELECT value FROM @temp WHERE id = 3), -- another - int
    (SELECT value FROM @temp WHERE id = 4) -- andAnother - int
)

SELECT * FROM @cid

note that i assumed you're using mssql, you did not specify

answered Sep 11, 2014 at 18:46

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

1 Comment

Kritner Over a year ago

this would be a way to do it, but i would rethink the way you're pulling your params if that's possible

Collectives™ on Stack Overflow

Assign multiple values to Table variable in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related