A way to T-SQL a cyclic table, t-SQL cyclic table

A way to T-SQL a cyclic table, t-SQL cyclic table

From: https://www.lesg.cn/netdaima/sqlservert-sql/2016-463.html

SsqlServer has several methods for circulating tables.

1. Temporary table

2. cursor

3 ....

The following describes how to use a temporary table to loop data.

Create table t (id int not null primary key identity (1, 1), dt datetime not null default (getdate (), name varchar (100) not null default ('')) -- Test Case: insert data into the table: declare @ count int; set @ count = 0; while (@ count <100) beginset @ count = @ count + 1 insert into t (name) values (NEWID () endselect * from t -- determine whether a temporary table exists. if yes, delete the temporary table if exists (select 1 from tempdb .. sysobjects where id = object_id ('tempdb .. # t_m') begin drop table # t_m -- delete the temporary table end -- insert data into the temporary table select * into # t_m from t -- start to loop table data declare @ tmid int; -- create a temporary variable While (exists (select 1 from # t_m) BEGINselect top 1 @ tmid = id from # t_m -- take out a piece of data and copy it in the temporary variable, use --/* to DELETE the data. Here, use @ tmid to operate the data. lesg.cn */-- DELETE # t_m where id = @ tmid; -- delete the data ENDif exists (select 1 from tempdb .. sysobjects where id = object_id ('tempdb .. # t_m') begin drop table # t_m -- delete the end of the temporary table after the operation ends.

The idea is as follows;
1. Create a temporary table
2. while loop temporary table; the loop condition is whether the temporary table exists
3. Get the data of a temporary table; remember to use top 1; otherwise, the performance of the data is as low as crazy as you get the temporary variable. The temporary variable is equal to the ID of the data.

  select top 1 @tmid =id from #t_m

4. Use temporary variables to operate data
5. Delete the temporary table after the entire cycle ends.