跨数据源导入数据 sql
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
-- 在目标库上执行以下语句,需要改 库地址以及连接用的, 用户名 、密码
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
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
走向地狱的途中,不小心走了程序员这条路,路上一个个黑心的老板,和暗无天日的加班,我才发现,通往地狱的路径中,我们这行是最短的。