Click here to Skip to main content
12,351,832 members (32,256 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

81.1K views
7 bookmarked
Posted

How to Use Update Cursors in SQL Server

, 28 Jul 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Using Update Cursors in SQL Server

There can be a situation where you have to use a cursor, even though the experts say not to use cursors or to avoid them as much as possible. But if you look closely, most of the time we use cursors to iterate through a row collection and update the same table.

In these type of situations, it is ideal to use a Update Cursor, than using the default read only one.

Consider the following table:

CREATE TABLE [dbo].[SAMPLE_EMPLOYEE](
    [EMP_ID] [int] NOT NULL,
    [RANDOM_GEN_NO] [VARCHAR](50) NULL
) ON [PRIMARY]

Insert few records to the above table using the following script:

SET NOCOUNT ON
DECLARE @REC_ID        AS INT

SET @REC_ID = 1

WHILE (@REC_ID <= 1000)
BEGIN
    INSERT INTO SAMPLE_EMPLOYEE
    SELECT @REC_ID,NULL
    
    IF(@REC_ID <= 1000)
    BEGIN
        SET @REC_ID = @REC_ID + 1
        CONTINUE
    END
    
    ELSE
    BEGIN
        BREAK
    END
END
SET NOCOUNT OFF

Next, we will add a Primary Key using the below script (or you can use the table designer):

ALTER TABLE [dbo].[SAMPLE_EMPLOYEE] _
ADD CONSTRAINT [PK_SAMPLE_EMPLOYEE] PRIMARY KEY CLUSTERED 
(
    [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,_ 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

** Please note: A primary key should be there if we are to use an update cursor. Otherwise the cursor will be read only.

Here is how you use the Update Cursor. I have highlighted the areas which differ compared with a normal cursor. You have to mention which column you are going to update (or all columns in your selection will be updatable) and you have to use ‘where current of <cursor>’ in your update statement.

img_scr_001_a

SET NOCOUNT ON
DECLARE 
    @EMP_ID                        AS INT, 
    @RANDOM_GEN_NO    AS VARCHAR(50),
    @TEMP                        AS VARCHAR(50)

DECLARE EMP_CURSOR CURSOR FOR
SELECT EMP_ID, RANDOM_GEN_NO FROM SAMPLE_EMPLOYEE FOR UPDATE OF RANDOM_GEN_NO
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
INTO @EMP_ID, @RANDOM_GEN_NO

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @TEMP =  FLOOR(RAND()*10000000000000)
    UPDATE SAMPLE_EMPLOYEE SET RANDOM_GEN_NO = @TEMP WHERE CURRENT OF EMP_CURSOR
    
    FETCH NEXT FROM EMP_CURSOR
    INTO @EMP_ID, @RANDOM_GEN_NO
END

CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR

SET NOCOUNT OFF

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

You may also be interested in...

Comments and Discussions

 
QuestionThanks saved my life.... Pin
Younus Mohammed13-Jan-16 21:47
memberYounus Mohammed13-Jan-16 21:47 
QuestionT SQL to Update as 1,2,3.. till 10 for each pid - Loop goes infinity Pin
sandzee_m721-Nov-15 20:09
membersandzee_m721-Nov-15 20:09 
QuestionHow to get the EMP_ID of current record of cursor Pin
crazyhemant12320-Apr-15 3:11
membercrazyhemant12320-Apr-15 3:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 28 Jul 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid