-- 方法1:使用表变量
-- 声明表变量
DECLARE @temp TABLE
(
empid INT,
firstname NVARCHAR(10),
lastname NVARCHAR(20)
);
-- 将源表中的数据插入到表变量中
INSERT INTO @temp(empid, firstname, lastname )
SELECT empid,firstname,lastname FROM HR.Employees
ORDER BY empid;
-- 声明变量
DECLARE
@empid AS INT,
@firstname AS NVARCHAR(10),
@lastname AS NVARCHAR(20);
WHILE EXISTS(SELECT empid FROM @temp)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1
SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM @temp;
UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;
SET ROWCOUNT 0
DELETE FROM @temp WHERE empid=@empid;
END
-- 方法2:使用临时表
-- 创建临时表
IF OBJECT_ID('tempdb.dbo.#tempemployees','U') IS NOT NULL DROP TABLE dbo.#tempemployees;
GO
SELECT empid,firstname,lastname
INTO dbo.#tempemployees
FROM HR.Employees
ORDER BY empid;
--SELECT * FROM dbo.#tempemployees;
-- 声明变量
DECLARE
@empid AS INT,
@firstname AS NVARCHAR(10),
@lastname AS NVARCHAR(20);
WHILE EXISTS(SELECT empid FROM dbo.#tempemployees)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1
SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM dbo.#tempemployees;
UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;
SET ROWCOUNT 0
DELETE FROM dbo.#tempemployees WHERE empid=@empid;
END