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
--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
-- do complex operation here
Insert into #tmp
SELECT @orderid,@orderqty
-- get next available row into variables
FETCH NEXT FROM insert_cursor into @orderid,@orderqty 
close insert_cursor
Deallocate insert_cursor

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.