Need help with a cursor - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with a cursor

Author

Topic

mcioffi
Starting Member

9 Posts

Posted - 2002-11-03 : 08:01:14

Hi Everyone,
I hate to admit this, but I'm having a terrible time writing this cursor. I have been working on Oracle for the last 2 years and cannot remember the proper t-sql, I'm now working in MSSQL again and need to get jump started.

Here is what I need to do:

Select the max value from table(table a) add 1 to it and call it say MAXID

Then I need to perform an insert into table a, including MAXID, based on something like

insert into table a
select @MAXID, b.acct+'_'+convert(char(10),c.sec),NULL,b.acct,
c.sec,'L',getdate(),getdate(),
1000000000,NULL,NULL,NULL,1000000000,0,1,1,NULL
from position b, Secur c
where b.sec = c.sec
and c.sec_typ = 'CURR'

The problem that I have been having is that everytime I try it, I get the same result for MAXID. The select query will produce any number of rows and I keep getting the first value for MAXID and repeating that for every insert.

What I need to do is find the MAXID, enter a loop, take the first row from the select, insert it, commit it, exit the loop and find the MAXID again, because it should have changed from the insert and re-enter the loop. I need to do this until I have processed all the rows from the select and then exit totally.

I really need some help with this.

Thanks everyone for your help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-03 : 08:09:31

I'm going to get screamed at for suggesting this, but why aren't you using an identity column? It will automatically increment each time a row or rows is inserted.

And I don't see a cursor anywhere in your code...(bravo!)

And why are you inserting all those NULLs, why not just include the necessary columns in the INSERT column list:

INSERT INTO tablea (ID, col1, col3, col4) ... --skip all of the null columns

mcioffi
Starting Member

9 Posts

Posted - 2002-11-03 : 08:16:26

Sorry,very disjointed. Thanks for the quick reply, by the way.

Not my table. Basically, I need to work within a the frame work of the info already given to me. I did not include the cursor I have been trying to write because I hosed it. Ihave thought about trying a temp table setting the identity equal to MAXID and going from there and then stuffing them back into table a.

The biggest problem I seem to be having wiht the cursor is that it has not been exiting my loop and getting the new value for MAXID it has been using the first found value of MAXID for all inserts. And since I'm so familiar with if..then..else I'm tripping myself up like crazy.

And why are you inserting all those NULLs, why not just include the necessary columns in the INSERT column list
Mainly because I was given the insert/select and was not as concerned about fixing that until I had the actual work (cursor or something else) done. Can I do a
BEGIN TRAN 1
create a temp table
set the identity equal to MAXID
insert into temp
then select into table a
COMMIT TRAN 1
drop temp table?

Edited by - mcioffi on 11/03/2002 08:22:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources