/* sql2005遍历表(方法1)
insert into 数据表(userid,adddate) values((select userid from 用户表),date);
*/
/*sql2005 表变量遍历表(方法2)*/
--表变量
declare @i int
declare @ChannelID int
declare @ChannelUpdateRoomCount int --nvarchar(50)
--遍历
set @i=(select min(XH_ChannelID) from XH_Channel where XH_ChannelState=0)
--遍历表中的每一行
while (@i<=(select max(XH_ChannelID) from XH_Channel where XH_ChannelState=0))
begin
select @ChannelID=XH_ChannelID,@ChannelUpdateRoomCount=XHService_UpdateRoomCount from XH_Channel where XH_ChannelState=0 and XH_ChannelID=@i
select @i=min(XH_ChannelID) from XH_Channel where XH_ChannelState=0 and XH_ChannelID>@i
--遍历ID，查找价格表不存在的数量批量插入
insert Tts_Hotel_PriceRoomState (Hrs_Date,Hrs_roomID,Hrs_HID, Hrs_ChannelID,Hrs_RoomCount,Hrs_optUser)
select Hp_Date,Hp_roomID,Hp_HID, @ChannelID,@ChannelUpdateRoomCount,'系统批量' from Tts_Hotel_Price left join Tts_Hotel_PriceRoomState
on Hp_Date=Hrs_Date and Hp_roomID=Hrs_roomID and Hrs_ChannelID=@ChannelID where Hrs_Id is null
end
