SQL 临时表或表变量替代游标

1.如果表没有自动增长的标识列（int） 使用临时表

SELECT IDENTITY(int) NewID ,.. INTO #tmp FROM YouTable

2.表有标识列 使用表变量

INSERT INTO @tmpTable
SELECT ..., NewID=(SELECT COUNT(*) FROM YouTable b WHERE b.aid<=a.aid) FROM YouTable a

当有了从 1到~d 的连续NewID时, 你就可以用循环来操作每一条记录了.
这个NewID你就可以当它是指针标识.

若不明白~~查看以下存储过程

/****** Object:  StoredProcedure [dbo].[aaa]    Script Date: 04/24/2015 15:11:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE TableVariablesReplaceCursor
as
    declare @tmpTable table(NewID int,OldID int,OldName nvarchar(200),OtherName nvarchar(200))
    Begin   
    INSERT INTO @tmpTable SELECT NewID=(SELECT COUNT(*) FROM cj_MoneyCenter b WHERE b.KeyId<=a.KeyId),KeyId ,GeneralName,MoneyName FROM YourTable a
    DECLARE @i INT,@cnt INT
    SET @i=1
    SELECT @cnt=COUNT(*) FROM YourTable
    WHILE @i<=@cnt
         BEGIN
               SELECT * FROM @tmpTable WHERE NewID=@i
               SET @i=@i+1
         END
    End 

 

转载于:https://www.cnblogs.com/AlbertJoey/p/4453640.html