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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.