SQL Server使用游标或临时表遍历数据

declare @ProductName nvarchar(50)
declare pcurr cursor for select ProductName fromProducts
open pcurr
fetch next frompcurr into @ProductName
while (@@fetch_status = 0)
begin
 print (@ProductName)
 fetch next frompcurr into @ProductName
end
close pcurr
deallocate pcurr  

declare @ProductName nvarchar(50)
declare @ProductID int
select @ProductID=min(ProductID) fromProducts
while @ProductID is not null
begin
 select @ProductName=ProductName from Products where
ProductID=@ProductID
 print(@ProductName);
 select @ProductID=min(ProductID) from Products where
ProductID>@ProductID
end

--删除临时表#Tmp
create table #Tmp  --创建临时表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    WokNo                varchar(50),   
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);
--declare @temp table   
--(   
--   [id] int IDENTITY(1,1),   
--   [Name] varchar(10)   
--)   
Select * from #Tmp    --查询临时表的数据
truncate table #Tmp  --清空临时表的所有数据和约束
相关例子：
Declare @Wokno Varchar(500)  --用来记录职工号
Declare @Str NVarchar(4000)  --用来存放查询语句
Declare @Count int  --求出总记录数      
Declare @i int
Set @i = 0
Select @Count = Count(Distinct(Wokno)) from#Tmp
While @i <@Count 
    Begin
       Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
       Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
       Select @WokNo,@i  --一行一行把职工号显示出来
       Set @i = @i + 1
    End
--drop table #temp

USE Test_DBData;
GO
--修正表中REC_CreateBy,REC_ModifyBy
CREATE TABLE #temp
    (
      id INT IDENTITY(1, 1) ,
      tablename NVARCHAR(100)
    );
DECLARE @tablename NVARCHAR(100);
DECLARE @n INT;
DECLARE @count INT;
DECLARE @str NVARCHAR(4000);
  --用来存放查询语句
DECLARE @tableCreateBy NVARCHAR(150);
DECLARE @tableModifyBy NVARCHAR(150);
SELECT  @n = 1;
INSERT  #temp
        ( tablename
        )
        SELECT  name
        FROM    sysobjects
　　　　 WHERE type = 'U '
AND (name <> 'Dim_Employee') 
AND (name <> 'Fct_ChannelType') 
　　　　AND (name <> 'Rel_TPOCommodityMessage')
AND (name LIKE 'Dim%' OR name LIKE 'Fct%' OR name LIKE 'Rel%');

SELECT  @count =@@rowcount;
WHILE @n <=@count
    BEGIN
        SELECT  @tablename =( SELECT   tablename
                               FROM     #temp
                               WHERE    id =@n
                             );
        SET @tableCreateBy = @tablename + '.REC_CreateBy';
        SET @tableModifyBy = @tablename + '.REC_ModifyBy';
        SET @str = 'IF EXISTS ( SELECT  *
            FROM    ( SELECT    '+@tableCreateBy+'
                      FROM      '+@tablename+'
                                INNER JOIN dbo.Dim_Employee ON '+@tableCreateBy+' =Dim_Employee.LoginName
                    ) tb)
    BEGIN
        UPDATE  '+@tablename+'
        SET     REC_CreateBy =Dim_Employee.EmployeeId
        FROM    '+@tablename+'
                INNER JOIN dbo.Dim_Employee ON '+@tableCreateBy+' =Dim_Employee.LoginName;
    END;    
    IF EXISTS ( SELECT  *
            FROM    ( SELECT    '+@tableModifyBy+'
                      FROM      '+@tablename+'
                                INNER JOIN dbo.Dim_Employee ON '+@tableModifyBy+' =Dim_Employee.LoginName
                    ) tb )
    BEGIN
        UPDATE  '+@tablename+'
        SET     REC_ModifyBy =Dim_Employee.EmployeeId
        FROM    '+@tablename+'
                INNER JOIN dbo.Dim_Employee ON '+@tableModifyBy+' =Dim_Employee.LoginName;
    END;    
    ';
EXEC(@str);
        SELECT  @n = @n + 1;
        DELETE  FROM #temp
        WHERE   tablename =@tablename;
    END;
--删除临时表
IF OBJECT_ID(N'tempdb.dbo.#temp') IS NOT NULL
    BEGIN
        DROP TABLE #temp;
    END

1.分批更新数据库
declare @x int
set @x=1 
while(@x<=51) 
begin 
begin tran 
update UserFavorite set UserFavorite.firstpublishtime =product.lastpublishtime
from UserFavorite,product where UserFavorite.productid =product.id 
and UserFavorite.id between (@x-1)* 10000 and @x*10000
commit tran
set @x=@x+1
WAITFOR DELAY '00:00:30'; --等待5秒
end