SQLserver使用映射表进行数据相关操作
时间：2021-07-01 10:21:17
帮助过：3人阅读
-----------------------------------------------
--Author:Oliver QIN
--Date:2015-11-07
--DESC:使用映射表对老数据进行更新
-------------------------------------------------
--存储老数据的表
--drop table TB_old
CREATE TABLE TB_old(Id
INT,IdSeq
VARCHAR(
20),Name
VARCHAR(
200),Adress
varchar(
200),Age
int)
--------------------------------------------------------------------
--插入测试数据（2014年的人员信息表）
---目前的编号是以省份来编号的，例如下面插入的测试数据
/*************************老的人力资源管理系统中******************************/
INSERT INTO TB_old
SELECT 1,
‘yunnan01‘,
‘JACK‘,
‘云南昆明‘,
45 union all
SELECT 2,
‘yunnan02‘,
‘Tom‘,
‘云南曲靖‘,
82 union all
SELECT 3,
‘yunnan03‘,
‘Alice‘,
‘云南丽江‘,
60 union all
SELECT 4,
‘yunnan04‘,
‘Jerry‘,
‘云南昭通‘,
26 union all
SELECT 5,
‘chongqin05‘,
‘Terry‘,
‘重庆江北‘,
22
---随着时间的变迁，需要把编号进行修改，统一为China开头的编号，例如：China01
--那接着就建立映射表
/*********************************映射表***************************************/
--drop table Mapping
CREATE TABLE Mapping (Old_IdSeq
varchar(
100),New_IdSeq
varchar(
100))
--插入映射关系
insert into Mapping
select ‘yunnan01‘,
‘China01‘ union all
select ‘yunnan02‘,
‘China02‘ union all
select ‘yunnan03‘,
‘China03‘ union all
select ‘yunnan04‘,
‘China04‘ union all
select ‘chongqin05‘,
‘China05‘
---这是2015年在新人力资源管理系统中新生成的一张信息表 注明：在老系统中已经生成的编号不可以替换成现有编号，需要延用至退休
--drop table TB_New 注：新的人力资源表中JACK的年龄更新为99，并且新加了JOSN的相关信息
CREATE TABLE TB_New(Id
INT,IdSeq
VARCHAR(
20),Name
VARCHAR(
200),Adress
varchar(
200),Age
int)
--插入新的测试数据
/*************************新的人力资源管理系统中******************************/
INSERT INTO TB_New
SELECT 1,
‘China01‘,
‘JACK‘,
‘云南昆明‘,
99 union all
SELECT 2,
‘China02‘,
‘Tom‘,
‘云南曲靖‘,
82 union all
SELECT 3,
‘China03‘,
‘Alice‘,
‘云南丽江‘,
60 union all
SELECT 4,
‘China04‘,
‘Jerry‘,
‘云南昭通‘,
26 union all
SELECT 5,
‘China05‘,
‘Terry‘,
‘重庆江北‘,
22 union all
SELECT 6,
‘China06‘,
‘JOSN‘,
‘广东深圳‘,
25
-------------现在有如下需求
/*********************************************************
随着时间的推移，需要实时的更新他们的相关信息,假设录入该年龄的时间是2014年
2015年系统进行升级，录入的相关信息则按新的规则命名编号，如果年龄大于45岁则不更新相关信息，视为退休
新的系统中已经把老的信息按照新的编码规则进行编码，但是这些信息需要用来上报给总经理查看。
*/
/**************************上报数据库中的信息*******************************************/
/*上报库中的信息是以前老的系统中的数据*/
--drop table Report_Rpt
CREATE TABLE Report_Rpt(Id
INT,IdSeq
VARCHAR(
20),Name
VARCHAR(
200),Adress
varchar(
200),Age
int)
INSERT INTO Report_Rpt
select * from TB_old
/**************************************************************************************
要求：使用新的人力资源管理系统中提供的数据对更新上报信息中的年龄
*/
--建立映射视图，用于关联更新
go
--select * from Differrence_Data
CREATE VIEW Differrence_Data
as
select A.Age,B.Old_IdSeq
from TB_New A
left join Mapping B
ON A.IdSeq
=B.New_IdSeq
left join TB_old C
ON C.IdSeq
=B.Old_IdSeq
WHERE A.Age
<>C.Age
---------------------------------------------------------
GO
--DROP PROC Get_NewInfo
CREATE PROC Get_NewInfo
AS
BEGIN
-------------------------------------------------------情况一：更新老编码对应的年龄信息
if((
select COUNT(
*)
from Differrence_Data)
>0)
-------------------------如果在视图Differrence_Data中存在数据，那么更新相应的老编码信息
UPDATE Report_Rpt
SET Age
=A.Age
FROM Differrence_Data A
WHERE A.Old_IdSeq
=Report_Rpt.IdSeq
IF ( (
select COUNT(
1)
from TB_New
where IdSeq
not in
(SELECT New_IdSeq
FROM Mapping
union all
select IdSeq
FROM Report_Rpt
)
) >0
)
-------------------------------------------------情况二：将新增的数据插入到上报表中
---插入新增的数据到上报信息表中
insert into Report_Rpt
select * from TB_New A
WHERE A.IdSeq
NOT IN (
SELECT New_IdSeq
FROM Mapping
UNION all
select IdSeq
FROM Report_Rpt)
------------------------------------------------情况三：更新新编码对应的年龄信息
IF (
(SELECT COUNT(
1)
FROM TB_New
WHERE
IdSeq not in
(select New_IdSeq
from Mapping)
AND IdSeq
IN
(SELECT IdSeq
FROM Report_Rpt)
)>0
)
--------------------更新新增加数据年龄变更的信息
UPDATE Report_Rpt
SET Age
=A.Age
FROM TB_New A
WHERE
A.IdSeq not in
(select New_IdSeq
from Mapping)
AND A.IdSeq
IN
(SELECT IdSeq
FROM Report_Rpt)
AND A.IdSeq
= Report_Rpt.IdSeq
END
--执行存储过程
EXEC Get_NewInfo
--老人力资源表
select * from TB_old
--新人力资源表
select * from TB_New
--映射表
select * from Mapping
--上报表
select * from Report_Rpt
--------------------------------------------
--删除表语句
--DROP TABLE TB_old,TB_New,Mapping,Report_Rpt
--------------------------------以下是测试老编码年龄变化跟新编码年龄变化还有新增新的人员信息时，是否也会更新跟插入相应的数据
--------------------------------------改变新人力资源系统中的数据进行逻辑测试
insert into TB_New
SELECT 7,
‘China07‘,
‘测试01‘,
‘云南昆明‘,
21 union all
SELECT 8,
‘China08‘,
‘测试02‘,
‘云南昆明‘,
33
---------------修改新数据进行测试
update TB_New
set Age
=‘100‘ where Id
=‘6‘
---------------修改老数据的年龄进行测试
update TB_New
set Age
=‘100‘ where Id
=‘1‘
SQLserver使用映射表进行数据相关操作
标签：
