Loop Through 2 Tables and Combine Data

I am trying to loop through 1 table and apply the values in from the dbo.SecTable into my dbo.Portfolio table so it returns every row contained dbo.SecTable for each individual account.  I know I can union but the values in my dbo.SecTable are way too many to union.

Is there a way I loop through dbo.SecTable to link it to the dbo.Portfolio table and return each SecID and SecName for every one AcctID?  Any assistance would be great!

select p. AcctID
, p.AcctName
from dbo.Portfolio p
C--Users-kwhitley-Documents-OneNote-Note
select s.SecID
, s.SecName
from dbo.SecTable s
C--Users-kwhitley-Documents-OneNote-Note

C--Users-kwhitley-Documents-OneNote-Note
KANEDA 0149Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
You will need to use Cross Apply or possible Outer Apply.

What are fields in both tables?

Portfolio:
AcctID
AcctName

SecTable:
?
Jim HornMicrosoft SQL Server Data DudeCommented:
So, 4 rows in Portfolio x 5 rows in SecTable = 20 rows in result set?  That's a CROSS JOIN.
SELECT p.AcctId, p.AcctName, s.SecID, s.SecName
FROM dbo.Portfolio p
   CROSS JOIN dbo.SecTable s

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
Try:
select p. AcctID , p.AcctName, d.SecID, d.SecName
from dbo.Portfolio p
CROSS APPLY (SecID, SecName FROM dbo.SecTable
WEHRE AcctID = p.AcctID) As d

Open in new window

Assuming AcctID = p.AcctID is joining column.

This post has been revised...

If yo want to have all from dbo.Portfolio regardless  dbo.SecTable has matching record in it or not, use:
select p. AcctID , p.AcctName, d.SecID, d.SecName
from dbo.Portfolio p
OUTER APPLY (SecID, SecName FROM dbo.SecTable
WEHRE AcctID = p.AcctID) As d

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

KANEDA 0149Author Commented:
My apologizes everyone, dbo.SecTable does not have 'AcctID' to associate it and is independant.  I was hoping there was a way to loop through dbo.SecTable and insert each values within it to the dbo.Portfolio even though they do not have a primary or foreign key to link the 2 tables.

Is this still possible?
Jim HornMicrosoft SQL Server Data DudeCommented:
Have you tried the CROSS JOIN / CROSS APPLY solutions?  If these do not work, tell us why in detail.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KANEDA 0149Author Commented:
Oh snap!  The cross join did exactly that.  Thank you so much Jim.
Mike EghtebasDatabase and Application DeveloperCommented:
This post is very helpful.

Take a look at "Top 10 Ways to Ask Better Questions".
KANEDA 0149Author Commented:
Sorry 'eghtebas' I thought I did in my screenshots the original request when I showed results 1 then results 2 and then expected results but I should have been more clearer in my question.  Thanks!
Mike EghtebasDatabase and Application DeveloperCommented:
You are right. You gave the result but not sample data in both dbo.SecTable and dbo.Portfolio so that we can see how to manipulate it.

Thanks,

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.