[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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
0
Bitadmin
Asked:
Bitadmin
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
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

0
 
SharathData EngineerCommented:
try this.
declare @Start  int
set @Start = 40100 
;with cte as (
Select recid2,ROW_NUMBER() over (order by createddate) rn 
  from notest2 WHERE createddate >= '2011-02-17' and createddate < '2011-02-18')
update cte
   set recid2 = rn+@Start

Open in new window

0
 
BitadminAuthor Commented:
thanks this worked great!
0
 
SharathData EngineerCommented:
>> thanks this worked great!

Then why B? Are you looking for something else?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now