declare @ParentID int
    
    declare @num int
    
    set @num =0
    
    --聲明一個游標mycursor,select語句中參數的個數必須要和從游標取出的變量名相同
    
    declare mycursor cursor for 
    
    select ProID as ParentID from Bms_Province p
    
    
    --打開游標
    
    open mycursor
    
    
    --從游標里取出數據賦值到我們剛才聲明的2個變量中
    
    fetch next from mycursor into @ParentID
    
    
    --判斷游標的狀態
    
    --0 fetch語句成功 
    
    ---1 fetch語句失敗或此行不在結果集中 
    
    ---2被提取的行不存在
    
    while (@@fetch_status=0)
    
    begin
    
    
    --顯示出我們每次用游標取出的值
    
     print '游標成功取出一條數據'
  
     set @num =@num+1
    
     print @num
    
    insert into Bms_Areas
    
    select 
    
    (case when T.CityID=0 then 0 else -99 end ) as ParentID,
    
    (case when T.CityID=0 then '0'+CONVERT(VARCHAR(10),T.ProID)
    
    else '00'+CONVERT(VARCHAR(10),T.CityID) end) as AreaCode,
    
    T.ProName as AreaName,'' as Notes,
    
    (case when T.CityID=0 then 1 else 2 end) as LevelNum,
    
    0 as IsDefault,1 as IsValid,0 as IsDel
    
    from (
    
     select p.ProID,p.ProName,p.ProSort,0 as CityID,p.ProRemark from Bms_Province p where ProID=@ParentID
    
     union select c.ProID,c.CityName,c.CitySort,c.CityID,'' as ProRemark from Bms_City c where ProID=@ParentID
    
     ) T order by CityID
  
     UPDATE T SET ParentID = 
    
    (select top 1 AreaID from Bms_Areas where ParentID=0 order by AreaID desc)
    
     from (select * from Bms_Areas where ParentID=-99 and AreaID> 
    
    (select top 1 AreaID from Bms_Areas where ParentID=0 order by AreaID desc)) T
  
    
    --用游標去取下一條記錄
    
     fetch next from mycursor into @ParentID
    
    end
    
    --關閉游標
    
    close mycursor
    
    --撤銷游標
    
    deallocate mycursor
  
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061
					微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
					
