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.

   

simple cursor in sql server to insert records

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

   

One Comment on “Simple cursor in SQL Server to insert records”

  1. Very helpful!
    Like you said it’s very simple. But it helped me build a more complex cursor with the same concept.

Leave a Reply

Your email address will not be published.