asked on

SQL cursor setup

I am trying to accomplish the following:

I have a SQL Server table with 8 columns. The last column is of int datatype. I need to generate a sequence number via sql code and insert a unique number into the int column in each row. I cannot insert any new rows in the table I need to update the existing rows. I cannot use the Identitiy function in SQL Server. Bottom line is I need a unique number in each. I began with the following code but I am missing something in my cursor because when I run the code below it updates each record with the same number. I am using SQL Sever 2008

DECLARE @Acctno Varchar(20), @recid2 int, @start int, @end int, @rownum int
DECLARE NOTEST2_Cursor CURSOR
FOR Select recid2 from notest2 WHERE createddate >= '2011-02-17' and createddate < '2011-02-18'
OPEN notest2_Cursor
set @rownum = 0
SET @Start = 40100
Set @end = 88500
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM notest2_Cursor INTO @recid2
BEGIN

       UPDATE NOTEST2
            set recid2 = @start
       FETCH NEXT FROM notest2_Cursor
into @recid2
Set @start = @start + 1
END;
CLOSE notest2_Cursor;
DEALLOCATE notest2_Cursor;
GO

Rajkumar Gs

To generate a unique number, you can use ROW_NUMBER()
See this example from http://www.dotnetspider.com/resources/2595-ROW-NUMBER-SQL-Server.aspx

create table #mytab ( Place VARCHAR(20))
INSERT INTO #mytab values ('First Record')
INSERT INTO #mytab values ('Second Record')
INSERT INTO #mytab values ('Third Record')
INSERT INTO #mytab values ('Fourth Record')
INSERT INTO #mytab values ('Final Record')
go
select row_number() over (order by Place) as ROWNUM, Place from #mytab
/*
ROWNUM PLACE
1 Final Record
2 First Record
3 Fourth Record
4 Second Record
5 Third Record
*/

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bitadmin

ASKER

thanks this worked great!

Sharath S

>> thanks this worked great!

Then why B? Are you looking for something else?