--删除临时表
if object_id('tempdb..##Temp') is not null
Begin
drop table ##Temp
End
--创建临时表
SELECT Row_Number() over(order by [CustomerID]) as id --(根据排序)生成序号
,[CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax]
into ##Temp
FROM [Northwind].[dbo].[Customers]
--遍历临时表
select id,[CustomerID],[CompanyName] from ##Temp
--遍历临时表中的每一行
declare @i int
declare @CustomerID nvarchar(50)
declare @CompanyName nvarchar(50)
set @i=(select min(id) from ##Temp)
while (@i<=(select max(id) from ##Temp))
begin
set @CustomerID=(select CustomerID from ##Temp where id=@i);
set @CompanyName=(select CompanyName from ##Temp where id=@i);
print cast(@i as nvarchar) + '...'+ @CustomerID + '...'+ @CompanyName
set @i=(select min(id) from ##Temp where id>@i);
end
备注:1.先通过Row_Number() over(order by [CustomerID])函数对原表进行(排序)编号,将查出的数据插入临时表##Temp,然后从最小序号开始遍历临时表的每一行数据。
2.操作的表是Northwind下的Customers表。