Solved

SQL cursor setup

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 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