?
Solved

creating an iterative loop in a temporary table where there is no ue Primary Key only a candidate key

Posted on 2011-10-06
2
Medium Priority
?
231 Views
Last Modified: 2012-05-12
I have a temporary table that I need to loop through in a function.

A snapshot of the data in the temporary table reveals the following:

@WasteStream
HeadId	AreaId	BranchId	SiteId	Description
11	12	16		8	Metal - Brass
11	12	16		8	Metal - Bronze
11	12	16		8	Metal - Contaminated
11	12	16		8	Metal - Copper
11	12	16		8	Metal - Iron
11	12	16		9	Metal - Brass
11	12	16		9	Metal - Bronze
11	12	16		9	Metal - Contaminated
11	12	16		9	Metal - Copper
11	12	16		9	Metal - Iron
11	12	16		10	Metal - Brass
11	12	16		10	Metal - Bronze
11	12	16		10	Metal - Contaminated
11	12	16		10	Metal - Copper
11	12	16		10	Metal - Iron

Open in new window


All the loop examples I have found on the internet so far have been simple examples that rely on using the primary key from a table as the cursor for the loop. In this case that isn't applicable.

As you can see there is no unique primary key that is going to be of any use, only a candidate key made up of SiteId and Description.

What I need is a loop that will loop through each INDIVIDUAL record in the temporary table and allow me to do the rather complicated processing required for each one (and believe me there is a lot of processing required for each one, hence the loop based solution rather than a set based solution).

Help would be much appreciated.
0
Comment
Question by:splanton
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36925879
Just add a row identifier column to the temp table.  You could add a RowID INTEGER IDENTIY(1,1) column.  Then when you insert data it will auto increment starting at 1.  

Greg

0
 
LVL 5

Expert Comment

by:almander
ID: 36925927
Here is what I would do.
1. Insert data into temp table with an identifier, (alternatively you could add an identity column to the current table too).

SELECT ROW_NUMBER() OVER(ORDER BY DESCRIPTION) RowID,
  HeadId,
  AreaId,
  BranchId,
  SiteId,
  Description
INTO #NEW_TEMP_TABLE

2. Loop through the data (I am not a fan of CURSORS, this method is MUCH faster)
Declare @CurrenRowtID int
Declare @LastRowtID int

SELECT @CurrenRowtID = 0

SELECT @LastRowtID = Max(RowID)
FROM #NEW_TEMP_TABLE

SELECT Top 1 @CurrenRowtID = RowID
FROM #NEW_TEMP_TABLE
WHERE RowID > @CurrenRowtID
ORDER BY RowID

WHILE @CurrenRowtID <= @LastRowtID
  BEGIN

  -- Do your work Here
  SELECT Top 1 @CurrenRowtID = RowID
  FROM #NEW_TEMP_TABLE
  WHERE RowID > @CurrenRowtID
  ORDER BY RowID

  END


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question