Cursor in SQL Server

Introduction:

Hello guys,

In the recent week i got  an opportunity to work with cursors in sql server. Although cursor is not new for me but the situation i got was new. I have given 2 tables suppose.: Customers and Orders. In the Customers table we had CustomerID varchar(4) field and the same field was in Orders table with  a foreign key relationship. I have been assigned a task to change that CustomerID fields from varchar to integer in both the tables. I google for some time to find out the solution for this situation but not saw a satisfactory result. The 1 of my friend suggest me to use cursor. Although i am not a big fan of cursor but in that scenario i had decided to use the same concept to fulfill my requirement.

Steps: I have taken following steps to perform this  task.

1. First of all i have taken a backup of both Customer and Orders table using select statement. Query i used for this purpose was.

“SELECT * INTO TEMPCUSTOMERS FROM CUSTOMERS”

“SELECT * INTO TEMPORDERS FROM ORDERS”

this staement create two new tables TEMPCUSTOMERS,TEMPORDERS and fill records to appropriate Customers and Orders tables.

2. Now i renamed the existing ‘CustomerID’ column into ‘ID’ and add a new column ‘CustomerID’ with Int datatype in Customer table .

3. The newly added CustomerID Int fields is made as identity field so that i fill records atomatically starting from 1 till record count.

4. In order table we had a column ‘CustomerID’  with varchar datatype having a foreign key relationship with Customers table.

5. Now i wrote a cursor which took newly added CustomerID and ID fields into two different variables and update the existing varchar value in Orders table with corresponding Interger value.

The syntax for the cursor i used was given below.

DECLARE @CUSTID VARCHAR(50)  — Took existing varchar CustomerID
DECLARE @ID INT — Took integer(identity) value from customers table

— DEFINE THE CURSOR
DECLARE CURSOR1 CURSOR FOR SELECT ID,CUSTOMERID FROM CUSTOMERS

— Select statement put respective CustomerID(varchar) and ID(int)  values into respective variables.

— WE SHOULD OPEN THE CURSOR
OPEN CURSOR1

— WE NEED TO FETCH THE ROWS INTO @EMPID VARIABLE
FETCH CURSOR1 INTO @ID,@CUSTID

–LOOPING
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE ORDERBACKUP SET ORDERBACKUP.CUSTOMERID = CONVERT(VARCHAR(20),@ID ) WHERE ORDERBACKUP.CUSTOMERID = @CUSTID
// This update statement mainly updates the  integer value from Customer table into Orders table

          FETCH CURSOR1 INTO @ID,@CUSTID
END
CLOSE CURSOR1

DEALLOCATE CURSOR1

 6. All done . To verify this task query the table to check updated record in Orders table.

Happy coding and God Bless SQL……………..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s