insert into Tb_XZH_Room(RoomInfo,Roomname,commid,roomid,BuildSNum,UnitSNum,IsDelete)
select case when CHARINDEX('单元',Roomname)-2>0 
then case when isnumeric(substring(Roomname,CHARINDEX('单元',Roomname)-2,2))=1 then substring(Roomname,CHARINDEX('单元',Roomname)-2,len(Roomname)) 
else substring(Roomname,CHARINDEX('单元',Roomname)-1,len(Roomname)) end
 when CHARINDEX('单元',Roomname)-1>0 then substring(Roomname,  CHARINDEX('单元',Roomname)-1 ,len(Roomname))
 else RoomName end as RoomInfo,Roomname,commid,roomid,BuildSNum,UnitSNum,IsDelete from Tb_HSPR_Room
 where   commid=601007  and roomid not in (select roomid from Tb_XZH_Room)