sql如何遍历几百万的表_SQL Server遍历表的几种方法

需求：针对Users表，新增一列fullname，并取值firstname+lastname

ALTER TABLE Users ADD fullname NVARCHAR(39) NULL;

GO

实现：UPDATE Users SET fullname=firstname+''+lastname;

一、使用游标

使用游标的代码比较繁琐，概括起来主要有以下几个步骤，声明游标，打开游标，使用游标，关闭游标和释放游标

-- 方法1：游标-- 声明变量DECLARE@uid AS INT,

@firstname AS NVARCHAR(10),

@lastname AS NVARCHAR(20);

-- 声明游标DECLARE U_Users CURSOR FAST_FORWARD FOR SELECT uid,firstname,lastname

FROM Users

ORDER BY uid;

OPEN U_Users;-- 取第一条记录

FETCH NEXT FROM U_Users

INTO@uid,@firstname,@lastname;WHILE@@FETCH_STATUS=0 BEGIN

-- 操作

UPDATE Users SET fullname=@firstname+''+@lastname WHERE uid=@uid;

-- 取下一条记录

FETCH NEXT FROM U_Users INTO@uid,@firstname,@lastname;

END

-- 关闭游标

CLOSE U_Users;

-- 释放游标

DEALLOCATE U_Users;

二、使用表变量

因为使用游标存在性能和违背面向集合思想的问题，所以我们有必要用面向集合的思想去找到一种更好的解决方案，下面这种方法是使用表变量的方式实现

-- 方法2：使用表变量

-- 声明表变量

DECLARE@tempTABLE

(

uid INT,

firstname NVARCHAR(10),

lastname NVARCHAR(20)

);

-- 将源表中的数据插入到表变量中

INSERT INTO@temp(uid, firstname, lastname )

SELECT uid,firstname,lastname FROM Users

ORDER BY empid;

-- 声明变量

DECLARE

@empid AS INT,

@firstname AS NVARCHAR(10),

@lastname AS NVARCHAR(20);

WHILEEXISTS(SELECTempidFROM@temp)

BEGIN

-- 也可以使用top 1

SET ROWCOUNT 1

SELECT @uid=uid,@firstname=firstname,@lastname=lastname FROM @temp;

UPDATE Users SET fullname=@firstname+''+@lastname WHERE uid=@uid;

SET ROWCOUNT 0

DELETE FROM@temp WHERE uid=@uid;

END

三、使用临时表

临时表也可以实现表变量的功能

-- 创建临时表

IF OBJECT_ID('tempdb.dbo.#users','U') IS NOT NULL DROP TABLE dbo.#users;

GO

SELECT uid,firstname,lastname

INTO dbo.#users

FROM Users

ORDER BY uid;

-- 声明变量

DECLARE

@uid AS INT,

@firstname AS NVARCHAR(10),

@lastname AS NVARCHAR(20);

WHILE EXISTS(SELECT uid FROM dbo.#users)

BEGIN

-- 也可以使用top 1

SET ROWCOUNT 1

SELECT @uid= uid, @firstname= firstname,@lastname= lastname FROM dbo.#users;

UPDATE Users SET fullname= @firstname+' '+@lastname WHERE uid=@uid;

SET ROWCOUNT 0

DELETE FROM dbo.#users WHERE uid=@uid;

END