How to Create Cursor, Temporary Table and Insert Data into a Temporary Table

In this article and SQL code, we will learn how to create a cursor on an existing table, and creating a temp table in a database. We will also learn how to insert data into a temp table.

Let's assume, we have an exisitng database table in SQL Server called Lottery. The table schema looks like the following:

TABLE COLUMNS..

Here the table with few rows in it.

ADD TABLE WITH DATA IN IT

Now, let's create a temp table named MatchTem. To learn more about temporary tables, read Temporary Tables in SQL Server.

-- Variable Declaration
DECLARE @id INT
DECLARE @BookName VARCHAR(MAX)
DECLARE @BookNumber VARCHAR(MAX)
DECLARE @BookNumberINT INT

DECLARE @Nextid INT
DECLARE @NextBookName VARCHAR(MAX)
DECLARE @NextBookNumber VARCHAR(MAX)
DECLARE @NextBookNumberINT INT

--
DECLARE @MatchTemp TABLE(
Id int,
BookName VARCHAR(MAX),
BookNumber VARCHAR(MAX),
SerialNumber VARCHAR(MAX),
Rate VARCHAR(MAX),
OpenValue VARCHAR(MAX),
AdminId int,
StoreNumber VARCHAR(MAX),
StoreId int,
BooksOnHand VARCHAR(MAX),
BooksReceived VARCHAR(MAX),
CloseValue VARCHAR(MAX),
Status VARCHAR(MAX),
Setvisible int,
SetDelete int,
CreateDate datetime
)

Let's creae another temp table named UnMatchTemp. The following SQL query will do the work.

DECLARE @UnMatchTemp TABLE(
Id int,
BookName VARCHAR(MAX),
BookNumber VARCHAR(MAX),
SerialNumber VARCHAR(MAX),
Rate VARCHAR(MAX),
OpenValue VARCHAR(MAX),
AdminId int,
StoreNumber VARCHAR(MAX),
StoreId int,
BooksOnHand VARCHAR(MAX),
BooksReceived VARCHAR(MAX),
CloseValue VARCHAR(MAX),
Status VARCHAR(MAX),
Setvisible int,
SetDelete int,
CreateDate datetime
)

Now we have two temp tables. Let's create a new cursor named db_Lotterycursor .

-- Cursor Declaration

DECLARE db_Lotterycursor CURSOR FOR

-- Query For Cursor

SELECT DISTINCT id ,
BookName,
BookNumber,
CAST(BookNumber AS INT) AS def
FROM Lottery
WHERE storeid='95' and Status='InActive'
ORDER BY CAST(BookNumber AS INT)

--Open Cursor
OPEN db_Lotterycursor

-- Go to Next Recored

FETCH NEXT FROM db_Lotterycursor INTO @id,@BookName,@BookNumber,@BookNumberINT
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM db_Lotterycursor INTO @Nextid,@NextBookName,@NextBookNumber,@NextBookNumberINT
IF(@BookNumberINT = @NextBookNumberINT AND @BookName = @NextBookName)
INSERT INTO @MatchTemp
(
Id,
BookName,
BookNumber,
SerialNumber,
Rate,
OpenValue,
AdminId,
StoreNumber,
StoreId,
BooksOnHand,
BooksReceived,
CloseValue,
Status,
Setvisible,
SetDelete,
CreateDate
)
SELECT [Id]
,[BookName]
,[BookNumber]
,[SerialNumber]
,[Rate]
,[OpenValue]
,[AdminId]
,[StoreNumber]
,[StoreId]
,[BooksOnHand]
,[BooksReceived]
,[CloseValue]
,[Status]
,[Setvisible]
,[SetDelete]
,[CreateDate]
FROM [dbo].[Lottery]
WHERE [Id] = @id OR [Id] = @Nextid

ELSE
INSERT INTO @UnMatchTemp
(
Id,
BookName,
BookNumber,
SerialNumber,
Rate,
OpenValue,
AdminId,
StoreNumber,
StoreId,
BooksOnHand,
BooksReceived,
CloseValue,
Status,
Setvisible,
SetDelete,
CreateDate
)
SELECT [Id]
,[BookName]
,[BookNumber]
,[SerialNumber]
,[Rate]
,[OpenValue]
,[AdminId]
,[StoreNumber]
,[StoreId]
,[BooksOnHand]
,[BooksReceived]
,[CloseValue]
,[Status]
,[Setvisible]
,[SetDelete]
,[CreateDate]
FROM [dbo].[Lottery]
WHERE [Id] = @id
FETCH NEXT FROM db_Lotterycursor INTO @id,@BookName,@BookNumber,@BookNumberINT
END
-- Close Cursor

CLOSE db_Lotterycursor

DEALLOCATE db_Lotterycursor
SELECT * FROM @MatchTemp
SELECT * FROM @UnMatchTemp

Summary

In this article, we learned how to create temp tables and add a cursor on two temp tables using SQL.

2 Comments

Join the conversation! Your thoughts help the community grow.

Ravi Kumar8y ago
I want to use this temporary table in my procedure,, suppose if i will execute my procedure automatically cursor will insert new data into my temporary table???
Markus Buhmann16y ago
And what exactly does this add to the SQL Server books online?