使用遊標進行跨資料庫迴圈更新

 

/*定義臨時變數*/

declare @Name nvarchar(20)

/*定義遊標*/

declare Emp_Cursor cursor for

 

select LastName+' '+ FirstName from AdventureWorks.HumanResources.vEmployee

 

open Emp_Cursor

 

fetch next from Emp_Cursor into @Name

while(@@Fetch_Status = 0)

begin

    insert into DB_CRM.dbo.Tab_Employee(EmpName)values(@Name)

    fetch next from Emp_Cursor into @Name

end

close Emp_Cursor

/*刪除遊標引用*/

deallocate Emp_Cursor

Select * from DB_CRM.dbo.Tab_Employee

/*這次主要用於資料庫升級！*/