cursor syntax - whats wrong?

[Lahorem]

Hi - The cursor below is suppose to copy data from a perm. table into a temp. table then make changes to the data and copy it back into the perm. table

Once in this temp table, I am trying to get the cursor to fill all gaps of column points as this column must be consecutive numbers.

Currently the table is something like this:


points type xxx

1 type7 123
2 type1 321
3 type3 654
5 type1 456
7 type9 987
15 type2 147
16 type2 789
17 type2 369
...
.....


Here is the code:

select * into #tmp_table from ProperTable

declare consecutive cursor for select * from #tmp_table

DECLARE @points ,@type   ,@xxx 

select @counter = 1

OPEN consecutive
FETCH YesterdayCursor 

INTO @points ,@type  ,@xxx

WHILE (@@sqlstatus = 0 )
begin
        insert into ProperTable values (@points ,@type ,@xxx)
        select @counter = @counter + 1
end

This code is obviously not compiling..

much grateful for any light you can shed for the newbie

[I have also tried doing this via sql update commands (from another thread) but this did not work so decided to go for the Cursor option.]

 

[simonchristieis]

use the temp table, I find it helps to declare the actual fields rather than use '*':

declare @tTable table(
 iRowID	int	identity(1,1),
 points varchar(50),
 type varchar(50),
 xxx  	int
)

Insert into @tTable(points,type,xxx)
 select points,type,xxx from ProperTable

declare @iCurrentRow int
select @iCurrentRow = 1

While 	@iCurrentRow <= ( Select count(*) From @tTable)
Begin

Update @tTable
set xxx = @iCurrentRow --(or whatever)
where @iRowID = @iCurrentRow 

Select @iCurrentRow = @iCurrentRow + 1
End

select * from @tTable

 

[chrissie1]

And would you want the result to look like?

Christiaan Baes
Belgium

"My new site" - Me

 

[Lahorem]

i don't actually have the rights to declare a perm. table hence I was using the # syntax to indicate a temp table.
Thanks for the code simonchristieis but the first bit doesnt work for me

chrissie1

Do you you mean alphabetical order?

 

[SQLSister]

Lahorem @tTable is not a permanent table it is a table variable. Table variables are generally faster than temp tables which is why he proposed using it. To specifically declare a #temp table then use the create table statement and specify the fields and datattypes.

One proble I see in your cursor is that you are opening a cursor of one name but trying to fetch from a different name. You have never defined or opened the cursor YesterdayCursor.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

Also what is this?
while (@@sqlstatus = 0)


Is this SQl Server code or are you using another DB?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/