Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL cursor setup

Posted on 2011-02-18
6
Medium Priority
?
381 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 41

Accepted Solution

by:
Sharath earned 2000 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 41

Expert Comment

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

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 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