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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *