Create temp table/loop through records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create temp table/loop through records

Author

Topic

billshankley1
Starting Member

11 Posts

Posted - 2008-06-19 : 07:45:58

Hi all

I'm new to sql and could do with some help resolving this issue.

My problem is as follows,

I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.

The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:

In a simple form it would look like this:

Level Ref: BomA

1 component A
1 component B
1 Bom D
1 component C

What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level

Which would in its simplest form look something like this

Level Ref: BomA

1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C

I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.

also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.

Bill Shankley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 07:49:07

http://support.microsoft.com/kb/248915

billshankley1
Starting Member

11 Posts

Posted - 2008-06-19 : 08:06:01

Thanks for the post,

its useful but setting a heirarchy for 1500 boms which could be any number of levels deep seems to be a bit of a showstopper.

I'm not sure about the ins and outs but should I be looking at doing something like opening a cursor and then fetching records where found in BOMHeaders?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 08:17:18

Use a recursive CTE if you use SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-19 : 08:17:53

Not really, all you need to do is expand on this idea. It really would depend on the structure of your materials tables. I am guessing that you do not have a heirachy level or if you do, then each bill of materials is at level 1. In this case, how would you know which one was the top level?

If there is a logical way to work this out then you shouldn't have too many issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources