What is a cursor?

From long time I wanted to know what cursors are ,I  did some googling and just came to know what cursor is  and here is the definition

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

But how to make it applicable in my practical life .

Then one day my friend came up with following requirement

 I Have 3 tables Tabel-1, Table-2, Table-3

--------------------------------------------------------------------------

 Table-1
Empid   Ename  salary

Here Empid is primary key

1         RAM       20k

2         SITA       30k

3         JOHN     40K

--------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
 Table-2
Empid   surname  commision

Here Empid is primary key

1         MANU  1k

2         DEVI      2k

3         ANDY    4K

4      SHAN    3K          

--------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------
Now  Table-3

Empid   Ename  salary  surname  commission

2         SITA               30k         DEVI      2K

Now my friend requirement is he need to get the matching records from  Table1 and Table 2

and insert in to Table3 ,if that record does not exists in Table -3,so  the records below is what I should insert in to Table-3

------------------------------------------------------------------------------------------------------

1         RAM       20k        MANU  1K

3      JOHN     40K         ANDY    4K

Now  ,I need to loop through the set of records which I get from joining the Table-1 and Table-2   and  manipulate every record to meet the requirement.

 secondly the query which I use to join Table-1 and Table-2    is

SELECT e.empid

FROM Table-1 e, Table-2     e1

WHERE e.empid=e1.empid

The result will be

------------------------------------------------------------------------------------------------------

1         RAM       20k        MANU  1K

2         SITA       30k         DEVI      2K

3      JOHN     40K         ANDY    4K

Now I don’t want  employee by name sita to be inserted in to Table-3.

So my next thought went to cursor bocz definition says that we can loop through the set of records.

Then I went ahead and found what CURSOR is .

So these  is how you should use cursor

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

Now this is how I did my requirement

DECLARE @empid int -- local variable to store primay key value 

declare @empcount int ---local variable to store the count

--Now i declare the cursor

DECLARE db_cursor CURSOR FOR

SELECT e.empid

FROM Table-1 e, Table-2 e1

WHERE e.empid=e1.empid

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @empid

WHILE @@FETCH_STATUS = 0 

BEGIN 

--here i check if it already exists in the destination table

select @empcount=count(*) from Table-3  where num_col=@empid

if @empcount=0

insert into Table-3 (empid) values(@empid );

else

print Can not insert;

   

       FETCH NEXT FROM db_cursor INTO @empid

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

---------------------------------------------------------------------------------------------------------------------
Finally Records in the Table 3 will be

------------------------------------------------------------------------------------------------------

1         RAM       20k        MANU  1K

2      SITA       30k         DEVI      2K

3      JOHN     40K         ANDY    4K

Please feel free  to post your comments

Happy coding.....