In this article I will explain with an example, how to insert data into Temp Table using
Cursor in
SQL Server.
Database
I have made use of the following table Customers with the schema as follow.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
Inserting data into Temp Table using Cursor
First, a Temp Table is declared with the columns and variables are declared for holding the data.
Then, the
Cursor is declared with a name and its type is set as
READ_ONLY and along with the FOR keyword the SELECT query is written.
Once the
Cursor is set, it is opened using the
OPEN command and the first record is fetched into the variables.
Note: Whenever a record is fetched, the @@FETCH_STATUS has value 0 and as soon as all the records returned by the SELECT query are fetched, its value changes to -1.
The
Cursor is associated with a WHILE loop which executes until the @@FETCH_STATUS value becomes 1.
Inside the WHILE loop, the current record is inserted into the Temp Table and then again the next record is fetched.
Finally, the
Cursor is closed and deallocated using CLOSE and DEALLOCATE commands respectively and the SELECT query is written to fetch the records from the Temp Table and after selecting the records the Temp Table is deleted.
Note: It is very important to deallocate a
Cursor, otherwise it will stay in 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 #Customers
(
[CustomerId] INT
,[Name] VARCHAR(100)
,[Country] VARCHAR(100)
)
--DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @CustomerId INT
,@Name VARCHAR(100)
,@Country VARCHAR(100)
--DECLARE THE CURSOR FOR A QUERY.
DECLARE InsertCustomers CURSOR READ_ONLY
FOR
SELECT [CustomerId], [Name], [Country]
FROM Customers
--OPEN CURSOR.
OPEN InsertCustomers
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM InsertCustomers INTO
@CustomerId, @Name, @Country
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Customers
([CustomerId]
,[Name]
,[Country])
VALUES
(@CustomerId
,@Name
,@Country)
--FETCH THE NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM InsertCustomers INTO
@CustomerId, @Name, @Country
END
--CLOSE THE CURSOR.
CLOSE InsertCustomers
DEALLOCATE InsertCustomers
-- SELECT DATA FROM TEMP TABLE
SELECT [CustomerId], [Name], [Country]
FROM #Customers
-- DROP TEMP TABLE
DROP TABLE #Customers
Screenshot
Downloads
