To update a table you can use an update statement why to use
cursor….?
Again How to update multiple values in a table. You can use case
when why to use cursor…?
This is difference from both scenarios that above mentioned. If we
want to update columns of a table from row by row in an iterating manner with
less query we can use cursor.
In the following example you will see how to implement a cursor
and the results we can obtain from that.
Following is the structure to declare a cursor.
Declare cursor
Open cursor for Loop(select a set of records)
{
Fetch record from cursor
}
Close cursor
Deallocate cursor
First let’s create a sample table.
create table Sample1
(ID int primary key, Given1 varchar(200),Surname varchar(200))
Insert into Sample1 (ID, Given1, Surname)
values (1,'john', 'smith'),(2,'eon', 'samuel'),(3,'sam', 'anderson')
select * from Sample1
Then declare a table with the values you want to input into the
above created table as below.
DECLARE @myList1 Table (Given1 varchar(200),Surname varchar(200))
insert into @mylist1 values(N'Stephanie',N'Edith');
insert into @mylist1 values(N'Lilian',N'Fernando');
insert into @mylist1 values(N'Jade',N'Chirnside');
select * from @myList1
After that declare the
cursor as below to loop through Given1 and Surname using ID to update the table.
--Declare the variables that need
to handle with the cursors
DECLARE @ID int
DECLARE @Given1 varchar(200)
DECLARE @Surname varchar(200)
--Declare first cursor to go
through ID
DECLARE MynewCursor CURSOR LOCAL
FOR
Select ID from Sample1
OPEN MynewCursor
FETCH NEXT FROM MynewCursor
INTO @ID
--Declare the second cursor to
update the columns
DECLARE MynewCursor1 CURSOR LOCAL
FOR
Select Distinct Given1,Surname from @myList1
OPEN MynewCursor1
FETCH NEXT FROM MynewCursor1
INTO @Given1,@Surname
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Sample1 SET Surname=@Surname, [Given1]=@Given1
where [ID]=@ID
FETCH NEXT FROM MynewCursor
INTO @ID
FETCH NEXT FROM MynewCursor1
INTO @Given1,@Surname
END
CLOSE MynewCursor
DEALLOCATE MynewCursor
CLOSE MynewCursor1
DEALLOCATE MynewCursor1
select * from Sample1
CLOSE MynewCursor1
DEALLOCATE MynewCursor1
select * from Sample1
