Insert data into Temp Table using Cursor in SQL Server

Introduction: Hello Guys, “Welcome back” Today, I am here with another one new great article. In this article I explained how to insert data into Temp Table using Cursor in SQL Server.

Join our Channel  on Whtsapp and Telagram for All Updates                                          

Database

I created the following table named userdetails with the all details as follows.

 Column name           data Type

 userid                                   int

 username                 varchar(100)

 useremail                 varchar(50)

useraddress             varchar(250)

I have already inserted few records in the table.

userid       username                 useremail                        useraddress                                      

1                   Sanjeev                  sanjeev@gmail.com    #250 Sector 127, Chandigarh         

2                   Sanjay                    sanjay@gmail.com       #123 Sector 255, Chandigarh         

3                   Ruhika                    ruhika@gmail.com       #256 Sector 855, Chandigarh         

4                   Rajan                      rajan@gmail.com         #55 Sector 00, Chandigarh              

Here I am also giving SQL Server Database Script, you can execute this SQL Database Script by using copy and paste to create table with column and data.

         SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[userdetails](

            [userid] [int] IDENTITY(1,1) NOT NULL,

            [username] [varchar](100) NOT NULL,

            [useremail] [varchar](50) NOT NULL,

            [useraddress] [varchar](250) NOT NULL

           CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [userid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

I have already inserted few records in the table.

Inserting data into Temp Table using Cursor

Firstly we will declare Temp Table using the columns and we will also declare variables for holding the data. After that we will declare the Cursor with a name and we will set the type of  Cursor as READ_ONLY using with the FOR keyword the SELECT query will also be  written. When Cursor will be set, then it will be opened using the OPEN command and the first record is fetched into the variables. When  a record will be  fetched, them the @@FETCH_STATUS has value 0 and all the records returned by the SELECT query using   Cursor are fetched, its value changes to -1. In the Cursor we will use WHILE loop which will be executes until the @@FETCH_STATUS value becomes 1.

In the WHILE loop the current record will be inserted into the Temp Table and then again the next record will be fetched and so on. Finally, after the fetched of last row of then table, then the Cursor will be closed and deallocated using CLOSE and DEALLOCATE commands  and the SELECT query will be  written to fetch the records from the Temp Table and after selecting the records the Temp Table is deleted. It is compulsory to deallocate a Cursor, otherwise it will not be close from the database and when you declare another Cursor with same name again, SQL Server will throw an error: A cursor with the name 'Cursor1' already exists.

--CREATING TEMP TABLE.

CREATE TABLE #userdetails

(

     [userid] INT

    ,[username] VARCHAR(100)

    ,[useremail] VARCHAR(100)

   ,[useraddressl] VARCHAR(250)

 

)

--DECLARE THE VARIABLES FOR HOLDING DATA.

DECLARE @userid INT

       ,@uername VARCHAR(100)

       ,@useremail VARCHAR(100)

     ,@useraddressVARCHAR(250)

--DECLARE THE CURSOR FOR A QUERY.

DECLARE Insertuser CURSOR READ_ONLY

FOR

SELECT [userid], [username], [useremail] , [useraddress]

FROM userdetails

--OPEN CURSOR.

OPEN Insertuser

--FETCH THE RECORD INTO THE VARIABLES.

FETCH NEXT FROM Insertuser INTO

@userid, @username, @useremail, @useraddress

 --LOOP UNTIL RECORDS ARE AVAILABLE.

WHILE @@FETCH_STATUS = 0

BEGIN

    INSERT INTO # Insertuser

           ([useriId]

           ,[userename]

           ,[useremail]

           ,[useraddress]

)

    VALUES

           (@userid

           ,@username

           ,@ useremail

          ,@ useraddress)

  --FETCH THE NEXT RECORD INTO THE VARIABLES.

     FETCH NEXT FROM Insertuser INTO

     @userid, @username, @useremail, @useraddress

END

--CLOSE THE CURSOR.

CLOSE Insertuser

DEALLOCATE Insertuser

-- SELECT DATA FROM TEMP TABLE

SELECT [CustomerId], [Name], [Country]

FROM #Customers

-- DROP TEMP TABLE

DROP TABLE #Customers

In above code, I explained how to insert data into Temp Table using Cursor in SQL Server. Bye and take care of yourself Developers. We will come back shortly with the new article.

Regards

Programming Hub