Sometimes there is a much need that you want to read records rows by rows, either in any scenario. Let's take a scenario in which you want to migrate the data from one table to another table.
Let suppose there are two tables one with name Employee and other with name Employee_Details, during the stimulus of the project apparently you have created only one table and i.e. Employee and you have store all the information of Employee accordingly. After some time you have got a requirement that we need to separate the details of Employees into a different table. So definitely there is always a question intended that how we will be taking care of our old data and we will be migrating the data in to a new table. So a simple answer to this question is Sql Server Cursors.
Yes, by using cursors we can migrate the data from one table to another by reading records rows by rows. How we can use cursors ??
--> Let suppose there are two tables one with name Employee and other with name Employee_Details, during the stimulus of the project apparently you have created only one table and i.e. Employee and you have store all the information of Employee accordingly. After some time you have got a requirement that we need to separate the details of Employees into a different table. So definitely there is always a question intended that how we will be taking care of our old data and we will be migrating the data in to a new table. So a simple answer to this question is Sql Server Cursors.
Yes, by using cursors we can migrate the data from one table to another by reading records rows by rows. How we can use cursors ??
Declare @CrsrVar Cursor
declare @FirstName varchar(128)
declare @LastName varchar(128)
declare @DOB datetime
declare @Gender varchar(6)
declare @EmployeeID bigint --Foriegn key
declare @Index int
-- First of all you need to define the cursor and there will be a query defined in it
Set @CrsrVar = Cursor For
Select EmployeeID,FirstName,LastName,DOB,Gender from Employee
Open @CrsrVar
-- It will now fetch the record from cursor and store in to variables
Fetch Next From @CrsrVar
Into @EmployeeID,@FirstName,@LastName,@DOB,@Gender
-- Fetch until the records get finished
While (@@FETCH_STATUS = 0)
BEGIN
insert into Employee_Details (EmployeeID,FirstName,LastName,DOB,Gender) values(@EmployeeID,@FirstName,@LastName,@DOB,@Gender)
-- if you need the primary key of the above inserted data then you can use it
--set @PatientId = @@IDENTITY
Fetch Next From @CrsrVar
Into @EmployeeID,@FirstName,@LastName,@DOB,@Gender
END
-- Simply close the cursor and deallocate it.
Close @CrsrVar
Deallocate @CrsrVar
Conclusion:
So, It's good to use cursors when dealing with row by row data, but there will be performance issues if cursor is used for cumbersome data.
