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, Sep 11 '14 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, Sep 11 '14 at 18:34
yes input_parm_val_tx represents all values...First is Regio, then segment...and so on...this order is fixed.. — Bokambo, Sep 11 '14 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, Sep 11 '14 at 18:38
Pivoting might be a good way to do this. Take a look at this example: http://blog.jontav.com/post/8344518585/convert-rows-to-columns-columns-to-rows-in-sql-server — ssn, Sep 11 '14 at 18:46
@user662285: I believe you want to insert those 5 values as a single row in CID table. Take a look at the answer posted by me below. Let me know, if it helps you. — shahkalpesh, Sep 11 '14 at 19:05

score 1 · Accepted Answer · edited May 23 '17 at 12:21

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.

score 0 · Answer 2 · answered Sep 11 '14 at 18:46

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

this would be a way to do it, but i would rethink the way you're pulling your params if that's possible — Kritner, Sep 11 '14 at 18:48

Assign multiple values to Table variable in SQL

2 Answers2