1 declare @Id uniqueidentifier
2 declare @UserId uniqueidentifier
3 declare @DepartmentName nvarchar(128)
4 declare @DepartmentId uniqueidentifier
5 declare @departmentCount int
6 declare cursor1 cursor for select Id from PersonSummary --定义游标cursor1
7
8
9 open cursor1
10
11 fetch next from cursor1 into @Id
12
13
14 while @@fetch_status=0 --判断是否成功获取数据
15 begin
16
17 select @UserId=userId from PersonSummary where id=@Id
18 select @departmentCount=COUNT(*) from PersonSummaryDepartment where PersonSummaryId=@Id
19 if (@departmentCount <1)
20 begin
21 insert into PersonSummaryDepartment(PersonSummaryId,DepartmentId,DepartmentName)
22 select @id,a.DepartmentId,b.Name from [RSGPMS] .dbo.DepartmentUserRef a,[RSGPMS] .dbo.Department b where a.UserId=@UserId and a.DepartmentId = b.Id
23
24 end
25 fetch next from cursor1 into @Id --将游标向下移1行
26 end
27
28 close cursor1 --关闭游标
29 deallocate cursor1
