求助~~各位前辈请帮忙看下这段sql有什么问题吗

Question

明明数据已经5条数据插入表变量中，
可是后面插入和更新操作数据条数为0
请各位大侠帮忙看看啊~~~~~~~菜鸟求助啊~
exec sp_addlinkedserver     'hr_dblink','','SQLOLEDB','10.1.155.91,1433'   
exec sp_addlinkedsrvlogin   'hr_dblink','false',null,'crunch','123'  goDECLARE @bd_psndoc TABLE -- 定义临时表  
(    
[id] int IDENTITY(1,1),
creator char(20),
dr smallint,
psncode varchar(40)
ts char(19)
)insert into @bd_psndoc
(creator,
dr,
ts,
psncode) select 
creator,
dr,
ts,
psncodefrom hr_dblink.hr57.dbo.bd_psndoc where datediff(day,ts,getdate())=0 Declare @currentIndex int  
Declare @totalRows  int  
Declare @creator char(20)
Declare @dr smallint
    Declare @psncode varchar(40)
Declare @ts char(19)

select @currentIndex=1  
select @totalRows=count(1) from @bd_psndoc
 
while(@currentIndex<=@totalRows)  
   
   begin   select    
@creator=creator,
@dr=dr,
@psncode=psncode,
@ts=ts,
from @bd_psndoc where id=@currentIndex  

      
      if exists(select * from hr_dblink.hr57.dbo.bd_psndoc where psncode=@psncode)   
      
  begin
  
      update hr.dbo.bd_psndoc set 
       creator=@creator,
dr=@dr,
psncode=@psncode,
ts=@ts
      where psncode=@psncode

  select @currentIndex=@currentIndex+1; 
  
  end
  
      else
      
begin

  insert into hr.dbo.bd_psndoc   
   ( creator,
  dr,
  psncode,
  ts)      
   values  
   (@creator,
@dr,
@psncode,
@ts)        select @currentIndex=@currentIndex+1;  
end

  end
go
EXEC SP_DROPSERVER 'hr_dblink','DropLogins'

Answer 1

while里面第一个select有问题吧，自己Debug看看

Answer 2

先看看linkserver联接通不通，建议把循环改成游标，这样的效率更高，游标的使用：http://blog.csdn.net/luminji/article/details/5130004
exec sp_addlinkedserver     'hr_dblink','','SQLOLEDB','10.1.155.91,1433'   
exec sp_addlinkedsrvlogin   'hr_dblink','false',null,'crunch','123'  goDECLARE @bd_psndoc TABLE -- 定义临时表  
(    
[id] int IDENTITY(1,1),
creator char(20),
dr smallint,
psncode varchar(40),
ts char(19)
)insert into @bd_psndoc
(
creator,
dr,
ts,
psncode
)select 
creator,
dr,
ts,
psncode
from hr_dblink.hr57.dbo.bd_psndoc
where datediff(day,ts,getdate())=0Declare  @currentIndex int,
 @totalRows  int,
 @creator char(20),
 @dr smallint,
 @psncode varchar(40),
 @ts char(19)set @currentIndex=1select @totalRows=count(1) 
from @bd_psndoc
 
while(@currentIndex<=@totalRows)
begin
select
@creator=creator,
@dr=dr,
@psncode=psncode,
@ts=ts
from @bd_psndoc 
where id=@currentIndex

    if exists(select 1 from hr_dblink.hr57.dbo.bd_psndoc where psncode=@psncode)   
begin
  update hr.dbo.bd_psndoc set 
       creator=@creator,
dr=@dr,
psncode=@psncode,
ts=@ts
  where psncode=@psncode   set @currentIndex=@currentIndex+1; 
  
end
    else begin   insert into hr.dbo.bd_psndoc   
  ( 
  creator,
  dr,
  psncode,
  ts
  ) 
  values (@creator,@dr,@psncode,@ts)     set @currentIndex=@currentIndex+1;  
endend
go
EXEC SP_DROPSERVER 'hr_dblink','DropLogins'