use Northwind
go
drop table backuptable
go
create table backuptable
(
-- Eid int identity(1,1),
Eid int,
Ename varchar (50) not null ,
--constraint pk_Eid primary key (Eid),
)
go
declare OrderCursor cursor --声明一个游标
for
select EmployeeID from Orders group by EmployeeID
go
declare @E_name varchar (50)
declare @OrdersCount int
declare @EmpID int
open OrderCursor--打开游标
fetch next from OrderCursor into @EmpID --先读到第一个记录
while(@@fetch_status=0)--判断是否读到了记录
begin
set @E_name=(select LastName+'-'+FirstName from Employees where EmployeeID=@empID)
set @OrdersCount=(select count(*) from Orders where EmployeeID=@empID)
insert into backuptable(Eid,Ename) values (@empID,@E_name)
fetch next from OrderCursor into @EmpID --读到下一个记录
end
close OrderCursor--关闭游标
DEALLOCATE OrderCursor--释放游标