Solved

SQL cursor setup

Posted on 2011-02-18
6
374 Views
Last Modified: 2012-05-11
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
Comment
Question by:Bitadmin
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34929331
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34930412
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
 

Author Closing Comment

by:Bitadmin
ID: 34945400
thanks this worked great!
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34945479
>> thanks this worked great!

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now