Several methods of SQL Server traversal table are reproduced

Several methods of SQL Server Traversal table

Read Catalogue

• Using cursors
• Working with Table variables
• Working with temporary tables

In the database development process, we often encounter the situation to traverse the data table, a reference to traverse the table, our first impression may think of the use of cursors, although the use of cursors is intuitive, but it does not conform to the principle of set-oriented operation, and performance is lower than the set-oriented. Of course, from the perspective of set-oriented operations, there are two ways to traverse the table, summed up, there are several methods of traversing the table.

1. Using cursors
2. Working with Table variables
3. Working with temporary tables

My demand is: for the Hr.employees table, add a column of FullName, and take the value firstname+lastname.

--demand is, add a column of FullName, value Firstname+lastnamealter TABLE HR. Employees ADD FullName NVARCHAR (+) NULL; GO

The original effect is as follows.

This requirement could have been done with an SQL statement, as shown in the following code. But in order to demonstrate the traversal of the table, I still use these three ways to implement.

Use TSQLFundamentals2008; Goupdate HR. Employees SET fullname= firstname+ "+lastname;

Back to top using cursors

The code that uses cursors is cumbersome, and it is summed up in the following steps, declaring cursors, opening cursors, using cursors, closing cursors, and releasing cursors. The sample code is as follows.

--Method 1: Cursor--DECLARE variable declare    @empid as INT,    @firstname as NVARCHAR (Ten),    @lastname as NVARCHAR;    --DECLARE CURSOR declare c_employees cursor Fast_forward for    SELECT empid,firstname,lastname from     HR. Employees    ORDER by Empid;    OPEN c_employees;--fetch 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]mpid;        --Remove a record    FETCH next from C_employees into @empid, @firstname, @lastname; end--Close cursor Close c_employees;--release cursor deallocate c_employees;

Run the script, as the effect.

We can see that we have achieved the desired effect.

Back to top using table variables

Because of the performance of using cursors and against the problem of set-oriented thinking, it is necessary to use a set-oriented idea to find a better solution, the following method is implemented using table variables, the code is as follows.

1--Method 2: Use table variable 2--declaration table Variable 3 DECLARE @temp table 4 (5     empid INT, 6     FirstName NVARCHAR (Ten), 7     LastName NVARCHAR (20) 8); 9 10--insert data from the source table into the table variable one by one insert into @temp (Empid, FirstName, LastName) a SELECT empid,firstname,lastname from HR.  Employees13 ORDER by empid;14 15--Declaring variable DECLARE17     @empid     as int,18 @firstname as NVARCHAR,     @lastname As NVARCHAR, while the     EXISTS (SELECT empid from @temp)     is BEGIN23--you can also use the top 124     SET ROWCOUNT     Lect @empid = empid, @firstname = firstname, @lastname = LastName from @temp, and     UPDATE HR. Employees set fullname= @firstname + "[email protected] WHERE [email protected];27     SET ROWCOUNT 028     DELETE from @temp WHERE [email protected];30 END

Back to top using temp table

Temporary tables can also implement the function of table variables, so we can also use temporary tables to implement this requirement, the code is as follows.

1--Method 3: Use temporary table 2--Create temporary table 3 IF object_id (' tempdb.dbo. #tempemployees ', ' U ') is not NULL DROP table dbo. #tempemployees; 4 GO 5  6 SELECT empid,firstname,lastname  7 into dbo. #tempemployees 8 from HR. Employees 9 ORDER by empid;10 one--select * from dbo. #tempemployees; 12 13--declaration variable DECLARE15     @empid as int,16     @fi Rstname as NVARCHAR, @lastname as NVARCHAR,     while     EXISTS (SELECT empid from dbo. #tempemployees) 20 BEGIN21     --You can also use the top 122     SET ROWCOUNT 123     SELECT @empid = empid, @firstname = firstname, @lastname = LastName FROM dbo. #tempemployees,     UPDATE HR. Employees set fullname= @firstname + "[email protected] WHERE [email protected];25     SET ROWCOUNT 026     DELETE from dbo. #tempemployees WHERE [email protected];28 END

Of course, the effect of the implementation is the same.

Several methods of SQL Server traversal table are reproduced