SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loop through table to pass variables?


Loop through table to pass variables?

Author
Message
DataAnalyst011
DataAnalyst011
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 429
I'm looking for a way to run the each of the values in a table to populate a single SELECT statement (which places the results into a final table). For instance, in the below script, I would like to avoid declaring @Prog three times. I want to run through ProgTbl, pick up the three variables, and pass them into the SELECT statement. The outcome in FinalTbl would be:

Total   Prog
2 AR-1
1 AR-9
3 AR-14


Below is an example script. Would someone mind pointing me in the right direction? Thanks, as always, for the help!

--Create tblCount
CREATE TABLE tblCount
(
ID CHAR(10) NOT NULL,
Prog VARCHAR(50) NOT NULL,
)
;

INSERT INTO tblCount
(
ID,
Prog
)

VALUES
('1', 'AR-1'),
('2', 'AR-1'),
('3', 'AR-9'),
('4', 'AR-14'),
('5', 'AR-14'),
('6', 'AR-14')
;

--Create Prog
CREATE TABLE ProgTbl
(
Prog VARCHAR(50) NOT NULL
)

INSERT INTO ProgTbl
(
Prog
)

VALUES
('AR-1'),
('AR-9'),
('AR-14')
;

--Create FinalTbl
CREATE TABLE FinalTbl
(
Total NUMERIC(4,0) NOT NULL,
Prog VARCHAR(50) NOT NULL
)
;

--Table checks
select * from tblCount; select * from ProgTbl; select * from FinalTbl
;


/*At this point I would like to run through each of the values in Prog and insert them into @Prog*/
INSERT INTO FinalTbl(Total, Prog)

SELECT COUNT(ID)
FROM tblCount
WHERE Prog = @Prog


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
Like this.


SELECT COUNT(ID), Prog
FROM tblCount
GROUP BY Prog



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DataAnalyst011
DataAnalyst011
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 429
Thanks for your response!

I should have made my example a little harder. My production project is much bigger, but I was trying to use the bare minimum so as not to cloud the request. I truly need to use values from a different table to pass into a single variable. So in the background a single SELECT statement would have a single variable (@Prog) and pass the three ProgTbl.Prog values to build the values in the FinalTbl. Does that make sense?
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4980 Visits: 4000
Actually Sean's code will give you the counts you're looking for, and in a more efficient manner than trying to get each one separately using the variable. If the ProgTbl restricts the Prog values or has Prog values that don't match any record in tblCount, then maybe do it as a join?
INSERT INTO FinalTbl(Total, Prog)
SELECT COUNT(*), p.Prog
FROM ProgTbl p
INNER JOIN tblCount c ON p.Prog = c.Prog
GROUP BY p.Prog


DataAnalyst011
DataAnalyst011
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 429
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a

and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
DataAnalyst011 (12/12/2012)
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a


All you have done here is insert whatever value is in your @prog variable for each row.


and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?


Your example is so abstract it is really hard to tell what you are doing. What about a temp table. or a cross apply to a cte?

If you can be a bit more specific in what you are doing we can come up with a way to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26336 Visits: 17556
Using your description in your last post here is some code that will do exactly what you said you want to happen.


create table X
(
Prog int
)

insert X
select 1 union all
select 2 union all
select 3 union all
select 4

create table Q
(
ResultColumn int
)

insert Q
select Prog
from X

select * from Q

drop table X
drop table Q



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4980 Visits: 4000
DataAnalyst011 (12/12/2012)
...In my production project, the values don't exist and I use the variables to print them into a row for a business process need...

OK, so if I'm understanding this then you still want the items from ProgTbl even if there are no matching records in tblCount? Then the join would be a LEFT OUTER JOIN like this:
INSERT INTO ProgTbl
(Prog)
VALUES
('XX-99')

INSERT INTO FinalTbl(Total, Prog)
SELECT COUNT(c.prog), p.Prog
FROM ProgTbl p
LEFT OUTER JOIN tblCount c ON p.Prog = c.Prog
GROUP BY p.Prog


sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7102 Visits: 4379
DataAnalyst011 (12/12/2012)
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a

and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?


If the maximum number of different possible values isn't large, maybe a PIVOT would work:


CREATE TABLE #X (
Prog INT
)
INSERT INTO #X VALUES (1), (2), (3), (4)

SELECT [1] AS N1, [2] AS N2, [3] AS N3, [4] AS N4
FROM #X
PIVOT (MAX(Prog) FOR Prog IN ([1], [2], [3], [4])) AS PVT

DROP TABLE #X



Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
DataAnalyst011
DataAnalyst011
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 429
These are helpful replies, and give me a little direction. I'm going to work on it some more. If I get stuck again, I'll trying and build exactly what's happening. Its fairly involved, but I think it would be worth it. Thanks a TON for the help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search