跨数据源导入数据 sql

-- 在目标库上执行以下语句，需要改 库地址以及连接用的， 用户名 、密码

exec sp_addlinkedserver @server = '192.168.0.135,1027'  -- 源数据库 
go

exec sp_addlinkedsrvlogin @rmtsrvname = '192.168.0.135,1027 ',
@useself = false,   
@locallogin = 'sa',     
@rmtuser = 'sa',   -- 用户名
@rmtpassword = '' -- 密码
go

insert into U_ADMIN.T_PD_USER_MASTER(UM_LOGIN_ID, UM_NAME, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT)
select NEWID(), um_name, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT from [192.168.0.135,1027].dei_net_szxq.dbo.user_master
go

select BMMC from U_ADMIN.T_PD_DEPARTMENT where BMMC in(
SELECT code_tbl.display FROM [192.168.0.135,1027].dei_net_szxq.dbo.code_tbl WHERE code_tbl.ename = 'department' 
) 
go
-- 更新部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = '0f884a6e-694a-4aa1-84db-494843ded9b4' where UM_DEPT = '0' -- 局长
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = '9ea19025-a228-4f43-9627-4baa19d86f61' where UM_DEPT = '1' -- 办公室  
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = 'a4f68bbb-be74-4ed8-8dc2-867d88cda49f' where UM_DEPT = '2' -- 开发处
-- 新系统中部门叫开发管理处，而老系统分为 开发处，和管理处
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = 'a4f68bbb-be74-4ed8-8dc2-867d88cda49f' where UM_DEPT = '3' -- 管理处，
-- 监察大队未找到对应关系
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = null where UM_DEPT = '4'
-- 原系统有两个名为监测站的部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = 'eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff' where UM_DEPT = '5'
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = 'eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff' where UM_DEPT = '6'
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = '0c7d83df-bd15-4f71-b090-e87edb5e8a01' where UM_DEPT = '7' -- 信访科
go
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = '69eaef6c-59bc-4264-ab86-265ab34c2e4d' where UM_DEPT = '0f884a6e-694a-4aa1-84db-494843ded9b4'
-- 其他用户没有找到权限的对应关系 ，统一设置成中心收文员, 原表和新表对应关系需要对应
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = '41d9cff3-0d2c-4447-a78e-af22c341e8d7' where 
UM_DEPT in ('9ea19025-a228-4f43-9627-4baa19d86f61' ,'a4f68bbb-be74-4ed8-8dc2-867d88cda49f','a4f68bbb-be74-4ed8-8dc2-867d88cda49f'
, 'eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff','0c7d83df-bd15-4f71-b090-e87edb5e8a01' ) and UM_ROLE is null
go
-- 关闭连接
sp_droplinkedsrvlogin @rmtsrvname = '192.168.0.135,1027', @locallogin = 'sa'   
go
sp_dropserver @server = '192.168.0.135,1027'
go