据测试由于delete操作,造成自增列断号,下面语句中@count和tmp表的rowindex对应不上。所以某些phone查不出来,插入为空 set @count=(SELECT TOP 1 rowcnt FROM sys.sysindexes WHERE id=OBJECT_ID('HouseTemp') ORDER BY rowcnt DESC )
--select Row_Number() over(order by Phone) RowIndex,* INTO Tmp --from HouseTemp
--CREATE INDEX IX_Tmp ON tmp(RowIndex)
while(@count>0) begin set @flag=0; --这里去更新House表中对应号码的数据。
select @Phone=Phone,@HouseType=isnull(HouseType,''),@DataType=DataType,@ProjectName=isnull(ProjectName,''), @Area=isnull(Area,'') FROM HouseTemp as tmp where tmp.RowIndex=@count
set @count=(SELECT TOP 1 rowcnt FROM sys.sysindexes WHERE id=OBJECT_ID('HouseTemp') ORDER BY rowcnt DESC )
--select Row_Number() over(order by Phone) RowIndex,* INTO Tmp
--from HouseTemp
--CREATE INDEX IX_Tmp ON tmp(RowIndex)
while(@count>0)
begin
set @flag=0;
--这里去更新House表中对应号码的数据。
select @Phone=Phone,@HouseType=isnull(HouseType,''),@DataType=DataType,@ProjectName=isnull(ProjectName,''),
@Area=isnull(Area,'') FROM HouseTemp as tmp
where tmp.RowIndex=@count