Loop through the rows in a Temp Table

[mattyboy2000]

Hi

I've populated a Temporary table with the records I need to process I then want to loop through the Rows and insert into two seperate tables. I know your going to tell me to use an INSERT with a SELECT but I dont think this is possible in our case.

I use a Stored procedure called getNextNumber that increaments a field and returns the next number of the sequence. I'm using this as the Primary key for one of the tables. And part of the key in the other:

NomTrans Table - PK (RecordNo)
NomTransVal Table - PK (RecordNo, CurrCode)

Therfor I need the SAME RecordNo for both tables.

The way I access the getNextNo Function is shown below:

EXEC @NewNo = getNextNo('FieldName')

I think I need to use a cursor but I have only seen examples where one field is used.

I hope you can help and I haven't just rambled aimlesly


Thanks


Matt

 

[cheyney]

Not really sure what you're trying to do here. What are the columns in your temporary table? What kind of logic do you employ in the sproc getNextNo? Exactly what information do you need to insert into each of the two tables? I didn't really understand your explanation of the problem.

Cheyney

 

[mattyboy2000]

Fair. I understand as I am confusing myself.

NomTrans holds the Nominal Transaction Details
NomTransVal hold the currency values that allow us to have the values in numerous currencies.

The Record Number field is used to link the two tables together

The Temporary table contains the details required for the NomTrans and NomTransVal tables (apart from the Record Number)

The getNextNumber function creats an SQLStatement dynamically that increaments the field passed to it (in a control table) and returns the new value. It is really a way for us to replicate the Auto Numer facility.

I hope this gives you a better insight into what I'm trying to do

Thanks matt

 

[cheyney]

what are the relevent column names in the temp table?
what is this 'field' that you pass into getNextNumber?

I'm still not really getting it maybe you could give some example rows from the temp table, and how you would like the results to appear in the two other tables.

Cheyney

 

[tlbroadbent]

Matt,

Are you using SQL 2000? If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[mattyboy2000]

Yes SQL Server 2000