SQL Server traverses the two methods recorded in the table (using Table variables and cursors)

Cursors are usually used to traverse tables on SQL Server. in SQL Server, you can easily use cursors to implement loops and traverse records in tables on SQL Server. This article describes how to use table variables and cursors to traverse tables in a database.

Table variable for table Traversal
In the following code, the differences between code blocks are marked with a gray background.Copy codeThe Code is as follows: DECLARE @ temp TABLE
(
[Id] int identity (1, 1 ),
[Name] VARCHAR (10)
)
DECLARE @ tempId INT,
@ TempName VARCHAR (10)
Insert into @ temp
VALUES ('A ')
Insert into @ temp
VALUES ('B ')
Insert into @ temp
VALUES ('C ')
Insert into @ temp
VALUES ('d ')
Insert into @ temp
VALUES ('E ')
While exists (SELECT [id]
FROM @ temp)
BEGIN
Set rowcount 1
SELECT @ tempId = [id],
@ TempName = [Name]
FROM @ temp
Set rowcount 0
-- Delete from @ temp where [id] = @ tempId
PRINT 'name: ---- '+ @ tempName
END

However, this method must use ROWCOUNT. However, using set rowcount may affect the DELETE, INSERT, and UPDATE statements.
So modify the while loop above and use TOP to select the first record.Copy codeThe Code is as follows: while exists (SELECT [id]
FROM @ temp)
BEGIN
Select top 1
@ TempId = [id],
@ TempName = [Name]
FROM @ temp
Delete from @ temp
WHERE [id] = @ tempId
SELECT *
FROM @ temp
EXEC ('drop table' +)
PRINT 'name: ---- '+ @ tempName
END

This method also has a problem. You need to delete the rows that have been traversed. In fact, we may not delete a row after traversing a row.
Use cursors to traverse tables
Cursors are very evil. Using cursors is often two to three times slower than using a collection-oriented method. When a cursor is defined as a large data volume, this ratio increases. If possible, try to use while, subquery, temporary tables, functions, table variables, and so on to replace the cursor. Remember, the cursor is always your last choice, not your first choice.Copy codeThe Code is as follows: -- Define table Variables
DECLARE @ temp TABLE
(
[Id] int identity (1, 1 ),
[Name] VARCHAR (10)
)
DECLARE @ tempId INT,
@ TempName VARCHAR (10)
DECLARE test_Cursor CURSOR LOCAL
SELECT [id], [name] FROM @ temp
-- Insert data value
Insert into @ temp
VALUES ('A ')
Insert into @ temp
VALUES ('B ')
Insert into @ temp
VALUES ('C ')
Insert into @ temp
VALUES ('d ')
Insert into @ temp
VALUES ('E ')
-- Open the cursor
OPEN test_Cursor
WHILE @ FETCH_STATUS = 0
BEGIN
Fetch next from test_Cursor INTO @ tempId, @ tempname
PRINT 'name: ---- '+ @ tempName
END
CLOSE test_Cursor
DEALLOCATE test_Cursor