Building an ARRAY and then LOOP through results

Question

Building an ARRAY and then LOOP through results

c2k

Ten Centuries

Points: 1090
More actions
June 13, 2011 at 12:26 am

#239056

Looking for information on how to structure the following using t-sql:
I am trying to get information of of the database where
Step 1: Create an Array to hold all values in the database that are found to match a specific criteria (like 'ABC123-n%'). Actually, I want to get the JobID for each item that matches 'ABC123-n%'
Step 2: Loop through each JOBID value of the array and build a results table base on the results of using ABC123-1 , ABC123-2, ABC123-3, etc.
Can someone provide pointers (eg, sample code :+) that will help me understand how to do this and perhaps even get started. After understanding these 2 steps I can use my existing sql to build the query I want.
Of course, If I am going about this the wrong way - I'm all ears!
Thank you,
CK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jayanth Kurup SSC Eights! Points: 829 More actions · Answer 1

Not sure if this is what your looking for but gave it a try anyway. Since your just looking to get a set of lookup values and then fetch the required data to be inserted into another table.

I have replaced the array with a subquery and the loop with a set based join based on the below assumption. You might want to add additional logic to the subquery to get the desired lookup values and then modify the join to get the complete set of columns that need to go into the result table.

INSERT INTO resulttable -- the target results table

SELECT mt.JOBID , mt.resultscolumns

FROM Maintable mt -- the columns that need to be inserted into the results table.

JOIN (

SELECT DISTINCT JOBID

FROM Maintables

WHERE JOBID like '%ABC123-%'

) tt -- The array

ON mt.Jobid = tt.JOBID -- The join replaces the loop assuming the set contains the data

-- all of it needs to go into the same table.

Don Halloran SSCertifiable Points: 5937 More actions · Answer 2

I don't really understand your question, but be very careful using words like "array" and "loop through" That's procedural talk, my friend. We're declarative around here 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

allmhuran (6/13/2011)
words like "array" and "loop through"

couldn't help but think of an old cowboy movie with someone saying "Them Thar is Fightin' Words!"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

SQL doesn't truly provide array capability, although it is quite good at applying operations against entire sets of data. Think of it as having the WHILE loop done automatically for you. If you could give us a brief example of some sample data and the output results you are trying to generate, everyone could do a much better job of addressing your issue and giving you some meaningful examples. Thanks.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

This thread should be moved into the Relational Theory forum 🙂

Try thinking of an array as a column turned on its side and go from there.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato