Solved

SQL cursor setup

Posted on 2011-02-18
6
373 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

11 Experts available now in Live!

Get 1:1 Help Now