同步数据库信息（存储过程+游标）

       项目基本都是基于现在已有的数据库进行开发，或者扩展，基本碰到的项目都是2~3个以上的数据库，头疼。一般做的比较基础或者常用的就是写下同步，比如员工，部门，角色，等等的一些同步，

     我的操作是直接创建存储过程，然后去设置SqlServer的定时任务，定时去执行这个存储过程，那么就实现了我的需求。

--创建存储过程
create procedure [dbo].[pro_BasicDataTransfer]
as

begin
	
	DECLARE @F_CompanyId nvarchar(50)='8d168d52-23f1-4743-b8b2-7f256add9b97' --公司
--用户 	
	insert into T_Base_User(F_UserId, F_EnCode, F_Account, F_Password, F_Secretkey, F_RealName, F_Mobile, 
	F_Birthday, F_Gender, F_DepartmentId, F_CompanyId, F_DeleteMark, F_EnabledMark, F_CreateUserId, F_CreateUserName, F_CreateDate)   
	select E.FItemID, E.FMobilePhone ,E.FMobilePhone, '3caa63ad3aef84c83fad7a0f502d8be1', '5ecccf68c782d90e', E.FName, E.FMobilePhone
	, E.FBirthday, case isnull(E.FGender, 0) when 1068 then 1 else 0 end  , E.FDepartmentID, @F_CompanyId, 0, 1, 'System', 'System', GETDATE()
	from   AIS2019..t_Emp E 
  where not exists( select 1 from T_Base_User u where cast(E.FItemID as varchar) = U.F_UserId )


--声明游标   
DECLARE MyCursorQ CURSOR 
FOR select E1.FItemID ,E1.FMobilePhone, E1.FName, 
  E1.FMobilePhone, E1.FBirthday,  case isnull(E1.FGender, 0) when 1068 then 1 else 0 end as FGender, E1.FDepartmentID from AIS2019..t_Emp E1
   where not exists( select 1 from T_Base_User U where cast(E1.FItemID as varchar(50)) = U.F_UserId and  
                               F_CompanyId = @F_CompanyId and
							    isnull(E1.FMobilePhone, '0') = isnull(U.F_Mobile, '0') and
							    isnull(E1.FDepartmentID, '0') = isnull(U.F_DepartmentId, '0') and
							    isnull(E1.FName, '0') = isnull(U.F_RealName, '0') and
							    isnull(E1.FBirthday, GETDATE()) = isnull(U.F_Birthday, GETDATE()) and
							    case isnull(E1.FGender, '0') when 1068 then 1 else 0 end = isnull(U.F_Gender, '0') and
							    isnull(E1.FShortNumber, '0') = isnull(U.F_Account, '0')
							   )		
--打开一个游标 
OPEN MyCursorQ
--循环一个游标 
DECLARE @FItemID nvarchar(2000), @FShortNumber nvarchar(2000), @FName nvarchar(2000), @FMobile nvarchar(2000), @FBirthday nvarchar(2000), 
@FGender nvarchar(2000), @FDeptNo nvarchar(2000)

--移动游标指向到第一条数据，提取第一条数据存放在变量中 , 
FETCH NEXT FROM MyCursorQ INTO @FItemID, @FShortNumber, @FName, @FMobile, @FBirthday, @FGender,  @FDeptNo
--如果上一次操作成功则继续循环 	
WHILE @@FETCH_STATUS =0						
BEGIN 
	Update T_Base_User set F_Mobile=@FMobile, F_DepartmentId=@FDeptNo, F_RealName=@FName, 
    F_Birthday=@FBirthday, F_Gender=@FGender, F_Account=@FShortNumber , F_EnCode=@FShortNumber where F_UserId=@FItemID
	  and F_CompanyId = @F_CompanyId 
FETCH NEXT FROM MyCursorQ INTO @FItemID, @FShortNumber, @FName, @FMobile, @FBirthday, @FGender, @FDeptNo
END 

CLOSE MyCursorQ
DEALLOCATE MyCursorQ 


-- 部门 
	insert into T_Base_Department(F_DepartmentId, F_CompanyId, F_EnCode, F_FullName, F_SortCode, 
	            F_DeleteMark, F_EnabledMark, F_CreateDate, F_CreateUserId, F_CreateUserName,F_ParentId)
	select FItemID, @F_CompanyId, FNumber, FName, FItemID, 
	       0, 1, GETDATE(), 'System', 'System',0 from AIS2019..t_Department D
	where not exists(select 1 from T_Base_Department B where cast(D.FItemID as varchar) = B.F_DepartmentId  )

--声明游标   
DECLARE MyCursorW CURSOR 
FOR select FItemID, FNumber, FName  from AIS2019..t_Department D
   where exists( select 1 from T_Base_Department B where B.F_DepartmentId = cast(D.FItemID as varchar) and  
                               F_CompanyId = @F_CompanyId and
							   FItemID <> B.F_DepartmentId or 
							   FNumber <>B.F_EnCode or
							   FName <>B.F_FullName)
									
--打开一个游标 
OPEN MyCursorW

--循环一个游标 
DECLARE @FItemID1 nvarchar(2000), @FNumber1 nvarchar(2000), @FName1 nvarchar(2000)
--移动游标指向到第一条数据，提取第一条数据存放在变量中 
FETCH NEXT FROM MyCursorW INTO @FItemID1, @FNumber1, @FName1
--如果上一次操作成功则继续循环 	
WHILE @@FETCH_STATUS =0						
BEGIN 
  Update T_Base_Department set F_EnCode=@FNumber1, F_FullName=@FName1
    WHERE F_DepartmentId=@FItemID1
  FETCH NEXT FROM MyCursorW INTO @FItemID1, @FNumber1, @FName1
END 

CLOSE MyCursorW 
DEALLOCATE MyCursorW

end