Simple cursor in SQL Server to insert records

This blog briefs about using a simple cursor in SQL Server to insert records. This is not a real world complex example; however it will help you to understand to use cursor for inserting records.

Create table #tmp
(
	SalesOrderID int,
	OrderQty int
)
GO
--simple cursor in sql server 
Declare @orderid int, @orderqty int
-- declare a cursor
DECLARE insert_cursor CURSOR FOR 
SELECT SalesOrderId,OrderQty from Sales.SalesOrderDetail
WHERE SalesOrderID=43659

-- open cursor and fetch first row into variables
OPEN insert_cursor
FETCH NEXT FROM insert_cursor into @orderid,@orderqty

-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN
-- do complex operation here
Insert into #tmp
SELECT @orderid,@orderqty
-- get next available row into variables
FETCH NEXT FROM insert_cursor into @orderid,@orderqty 
END
close insert_cursor
Deallocate insert_cursor
GO

The above query creates a temporary table #tmp. It then declares an insert_cursor to iterate through rows of Sales.SalesOrderDetail table and gets values of salesorderid and orderqty into @orderid and @orderqty variables respectively. It then inserts the variable values in #tmp table until all rows are exhausted. Query the #tmp table to verify the insert as shown below.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook