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.