/* 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