如何取得游标当前行的一个列值？

[fttcccc]

--tblBasic表
USE cardtown
GO
declare @sum int,@PId nvarchar(50)
declare tblBasic_Cursor cursor for
SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic) --创建一个游标,并提取所需行

open tblBasic_Cursor

fetch next from tblBasic_Cursor
set @sum = 1
while @@FETCH_STATUS = 0
begin
insert into Log_tblBasic(PId,flag,updatetime) values(@PId,'add',dateadd(n,-@sum,getdate()))
set @sum = @sum + 10
fetch next from tblBasic_Cursor into @PId
end

close tblBasic_Cursor

deallocate tblBasic_Cursor
go

上面是代码，这样他只会执行一行，而且@PId 变量中还没有值，请大家帮帮忙看看，我该如何改，
我想要做的是循环游标结果集，把每一行的chrFemaleID值都插入到另一张表中.

[fttcccc]

自己先顶下，九点结贴，请高手帮忙

[marco08]

--tblBasic表
USE cardtown
GO
declare @sum int,@PId nvarchar(50)

declare tblBasic_Cursor cursor for
SELECT chrFemaleID
FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic) --创建一个游标,并提取所需行

open tblBasic_Cursor

--fetch next from tblBasic_Cursor 少了into?
fetch next from tblBasic_Cursor into @PId
set @sum = 1
while @@FETCH_STATUS = 0
begin
insert into Log_tblBasic(PId,flag,updatetime) values(@PId,'add',dateadd(n,-@sum,getdate()))
set @sum = @sum + 10
fetch next from tblBasic_Cursor into @PId
end

close tblBasic_Cursor

deallocate tblBasic_Cursor
go

[fish_yht]

USE cardtown
GO
declare @sum int,@PId nvarchar(50)
declare tblBasic_Cursor cursor for
SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic) --创建一个游标,并提取所需行

open tblBasic_Cursor

fetch next from tblBasic_Cursor into @PId
set @sum = 1
while @@FETCH_STATUS = 0
begin
insert into Log_tblBasic(PId,flag,updatetime) values(@PId,'add',dateadd(n,-@sum,getdate()))
set @sum = @sum + 10
fetch next from tblBasic_Cursor into @PId
end

close tblBasic_Cursor

deallocate tblBasic_Cursor
go

lz的这条语句插入到表里面了
lz要看看实际表中有没有数据
你用into相当于insert
怎么会显示给你结果呢

[sgucxc0]

SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic)

改为:
SELECT distinct chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18))

用distinct 就可以排除已插入另一个表的PID了.

[zsl5305256]

USE cardtown
GO
declare @sum int,@PId nvarchar(50)
declare tblBasic_Cursor cursor for
SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic) --创建一个游标,并提取所需行

open tblBasic_Cursor

fetch next from tblBasic_Cursor into @PId
set @sum = 1
while @@FETCH_STATUS = 0
begin
insert into Log_tblBasic(PId,flag,updatetime) values(@PId,'add',dateadd(n,-@sum,getdate()))
set @sum = @sum + 10
fetch next from tblBasic_Cursor into @PId
end

close tblBasic_Cursor

deallocate tblBasic_Cursor
go

[fttcccc]

谢谢两位，但改你们这样的，
还是只能插入到另一表里一条数据，而我源表中有10万条数据左右呢

[hellowork]

改成fetch next from tblBasic_Cursor into @PId应该没问题的.
请楼主单独运行一下下面的代码看看返回的结果集有多少行:
SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic)

[marco08]

楼主，你先查一下符合条件的数据

[fttcccc]

有132005条数据，但只有一条插入到另一个表中

[marco08]

SELECT chrFemaleID FROM tblbasic WHERE ((len(chridentity)=15) or (len(chridentity)=18)) and chrFemaleID not in
(select PId from Log_tblBasic) --创建一个游标,并提取所需行


--以上SQL运行后，返回132005条数据？

[fttcccc]

是呀，现在Log_tblBasic 表中还没有数据呢
10几万条数据的人员编号要插过去的

[fttcccc]

好了，谢谢各位兄弟了，九点整结贴，哈哈，结完贴就回家咯

[hellowork]

在游标定义前加上 SET ROWCOUNT 0试试,例如:
USE cardtown
GO
declare @sum int,@PId nvarchar(50)
SET ROWCOUNT 0
......

[hellowork]

或者这样测试一下:
...
open tblBasic_Cursor
fetch next from tblBasic_Cursor into @PId
set @sum = 1
while @@FETCH_STATUS = 0
begin
print @PID /*这样测试,看看是否打印132005条数据*/
fetch next from tblBasic_Cursor into @PId
end
...