SQL Server traverses data using table variables or temporary tables

Time:2021-4-16

1. SQL script usageTable variablesTraversal data example:

--Table variable 1
declare @tempTb Table(ID int, Name nvarchar(64))

--Table variable 2
declare @DtTb Table(ID int, Name nvarchar(64))

insert into @DtTb select top 100 ID,Name from [dbo].[Students]

--Declare the variable of the loop
declare @ID int;

--Loop through ID
while exists(select ID from @DtTb)
begin
    Set rowcount 1; -- it is valid for fetching data from the whole session, that is, if there is a query statement below, only one query statement can be fetched
    --Select top 1 @ id = ID from @ DTTB; -- use top 1 to query this sentence
    select @ID = ID from @DtTb;   

    --Specific traversal business
    insert into @tempTb select * from @DtTb where [email protected];

    Set rowcount 0; -- unqualify
    --Traverse a must delete this article, otherwise dead loop!
    delete from @DtTb where [email protected];
end

select * from @tempTb;delete from @tempTb;

 

2. SQL script usagecursorTraversal data example:

--Provisional Table 1
create Table #tempTb(ID int, Dbo nvarchar(64))

--Provisional Table 2
create Table #DtTb(ID int, Dbo nvarchar(64))

insert into #DtTb select top 100 ID,Dbo from [AnyImageGuLou02].[grid].[BHosCheckES]

--Declare the variable of the loop
declare @ID int;

--Loop through ID
while exists(select ID from #DtTb)
begin
    --Set rowcount 1; -- it is valid for fetching data from the whole session, that is, if there is a query statement below, only one query statement can be fetched
    Select top 1 @ id = ID from DTTB; -- use top 1 to query this sentence
    --select @ID = ID from #DtTb;   

    --Specific traversal business
    insert into #tempTb select * from #DtTb where [email protected];

    --Set rowcount 0; -- unqualify
    --Traverse a must delete this article, otherwise dead loop!
    delete from #DtTb where [email protected];
end

select * from #tempTb;
--select * from #DtTb;

--Remember to delete the temporary table after use!
drop table #tempTb;
drop table #DtTb;