Ways to traverse records in a table in SQL Server

There are several ways to traverse a table

1. Using Cursors

2. Using Table variables

3. Using temporary tables

The following is an example of the implementation of the three methods:

1. Demand

Assign a value to the Hr.employees table, fullname column, whose value is Firstname+lastname

In order to demonstrate the traversal of the table, UPDATE HR is ignored. Employees SET fullname= FirstName+" +lastname; The way to implement

2. Using Cursors

The code that uses the cursor consists of the following steps, declaring the cursor, opening the cursor, using the cursor, closing the cursor, and releasing the cursor.

--  Method 1: Cursor--  declaring variable declare     @empid  as int,    @ Firstname as nvarchar (,     @lastname  as nvarchar ();     --  declaring Cursors declare c_employees cursor fast_forward for     select empid,firstname,lastname     from hr. employees    order by empid;    open c_employees;--   Take the first record fetch next from c_employees into  @empid, @firstname, @lastname; while @ @FETCH_STATUS =0begin    --  Operation     update hr. employees set fullname=  @firstname + '   ' [email protected] where [email  protected];        --  Remove a record     FETCH  next from c_employees into  @empid, @firstname, @lastName end--  Close cursor close c_employees;--  release cursor deallocate c_employees;

3. Using Table Variables

--  Method 2: Use table variable--  declaration table variable declare  @temp  table (    empid INT,     firstname nvarchar (Ten),     lastname nvarchar ());--  Inserts data from the source table into the table variable insert into  @temp (empid, firstname, lastname ) Select empid, Firstname,lastname from hr. employeesorder by empid;--  declaring variables declare     @empid  AS INT,      @firstname  as nvarchar,     @lastname  as nvarchar     while exists (select empid from  @temp) begin     --  can also use Top 1    set rowcount 1    select   @empid = empid,  @firstname = firstname, @lastname = lastname from  @temp;     update hr. employees set fullname=  @firstname + '   ' [Email protected] where [email protected];    set rowcount 0         DELETE FROM  @temp  WHERE [email protected]; END

4. using temporary tables

--  Method 3: Use temporal table--  to create a temporary table if object_id (' tempdb.dbo. #tempemployees ', ' U ')  is not null  drop table dbo. #tempemployees; Goselect empid,firstname,lastname into dbo. #tempemployeesFROM  hr. Employeesorder by empid;--select * from dbo. #tempemployees;--  declaring Variables declare      @empid  AS INT,     @firstname  as nvarchar (Ten),      @lastname  as nvarchar (    while exists), select  Empid from dbo. #tempemployees) begin    --  can also be used top 1     SET ROWCOUNT 1    SELECT  @empid = empid,  @firstname =  FirstName, @lastname = lastname from dbo. #tempemployees;     update hr. employees set fullname=  @firstname + '   ' [email protected] where [email protected];    set rowcount 0         Delete from dbo. #tempemployees  WHERE [email protected]; END

Reference: Methods for logging in SQL Server traversal tables http://www.studyofnet.com/news/832.html

Ways to traverse records in a table in SQL Server