SQLserver使用临时表代替游标遍历_sql 游标和临时表的区别

使用游标做遍历操作

DECLARE @AssessThemeID INT
DECLARE @EntityID INT

DECLARE MyCursor CURSOR  --定义游标
FOR
    SELECT ContactID,AssessThemes.ID AS AssessThemeID
    FROM AssessWorkLoadAudit LEFT JOIN AssessThemes 
    ON AssessWorkLoadAudit.AssessThemeID=AssessThemes.ID 
    WHERE AssessThemes.ID IN 
    (
    SELECT ID FROM AssessThemes WHERE YearNum=2016 OR YearNum=2015
    )
OPEN MyCursor  --打开游标
FETCH FROM MyCursor INTO @EntityID,@AssessThemeID  --取游标第一行，将字段值赋予@EntityID和@AssessThemeID
WHILE(@@FETCH_STATUS=0)
BEGIN
    IF(@EntityID IS NOT NULL AND @AssessThemeID IS NOT NULL)
    BEGIN
        --执行带参的存储过程 AnnualSummaryUpdateAssessTabletest
        EXEC AnnualSummaryUpdateAssessTabletest @EntityID,@AssessThemeID
    END
    FETCH FROM MyCursor INTO @EntityID,@AssessThemeID  --取游标的下一行并将字段值赋予@EntityID和@AssessThemeID
END
CLOSE MyCursor  --关闭游标
DEALLOCATE MyCursor   --释放游标
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

使用临时表做遍历操作

临时表：临时表的名称以“#”开头，临时表储存在tempdb这个系统数据库当中

临时表如何实现遍历操作？

在根据查询结果创建临时表时，在临时表中加入一列标识列，标识列数据类型为整形，且取值为一个“从1开始以1递增”的序列。形如“1、2、3、4、5、·······”，这个序列可以唯一标识每一行，通过循环定位到某一行，对行进行相应的操作。
标识列：IDENTITY(INT) AS NewID
脚本如下：

--创建临时表#AWLA
SELECT IDENTITY(INT) AS NewID,* INTO #AWLA 
FROM (SELECT ContactID,AssessThemes.ID AS AssessThemeID
    FROM AssessWorkLoadAudit LEFT JOIN AssessThemes 
    ON AssessWorkLoadAudit.AssessThemeID=AssessThemes.ID 
    WHERE AssessThemes.ID IN (SELECT ID FROM AssessThemes WHERE YearNum=2016 OR YearNum=2015)
    ) AS A

DECLARE @EntityID INT
DECLARE @AssessThemeID INT
DECLARE @NewID INT
DECLARE @RowCount INT 
SELECT @NewID=1,@RowCount=MAX(NewID) FROM #AWLA 
WHILE @NewID<=@RowCount
BEGIN
    SELECT @EntityID=ContactID,@AssessThemeID=AssessThemeID FROM #AWLA WHERE NewID=@NewID
    EXEC AnnualSummaryUpdateAssessTabletest @EntityID,@AssessThemeID
    SET @NewID=@NewID+1
END
--删除临时表
DROP TABLE #AWLA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21