Basic requirements: old data has the sequence encoding rules for old data and new data has the sequence encoding rules for new data. However, the encoding of old data must be updated accordingly, new data must also be updated in real time. the old data pairs have been encoded using the new encoding rules in the new data. in the report, the basic requirements are as follows:
Old data has old data sequence encoding rules, new data has new data sequence encoding rules, but old data encoding still needs to be updated, and new data must also be updated in real time, the new data encoding rules have been used to encode the old data pairs. in the report, both new data and existing data must be added.
-------------------------------------------------
-- Author: Oliver QIN
-- Date: 2015-11-07
-- DESC: update old data using a ing table
-------------------------------------------------
-- Tables that store old data
-- Drop table TB_old
Create table TB_old (Id INT, IdSeq VARCHAR (20), Name VARCHAR (200), Adress varchar (200), Age int)
--------------------------------------------------------------------
-- Insert test data (personnel information table for April 2014)
--- The current number is numbered by province, for example, the test data inserted below
******* ***********************/
Insert into TB_old SELECT 1, 'yunnan01', 'Jack', 'Yunnan Kunming ', 45 union all
SELECT 2, 'yunnan02', 'Tom ', 'Yunnan Qujing', 82 union all
SELECT 3, 'yunnan03', 'Alice ', 'Yunnan Lijiang', 60 union all
SELECT 4, 'yunnan04 ', 'Jerry', 'Yunnan Zhaotong ', 26 union all
SELECT 5, 'chongqin05 ', 'Terry', 'Chongqing Jiangbei ', 22
--- As time changes, the numbers must be changed to numbers starting with China, for example, China01.
-- Create a ing table.
/********************************** Ing table **** ***********************************/
-- Drop table Mapping
Create table Mapping (Old_IdSeq varchar (100), New_IdSeq varchar (100 ))
-- Insert ing
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'
--- This is a new information table generated in the new human resources management system on April 9, 2015. note: The number generated in the old system cannot be replaced with the existing number, which needs to be extended to retirement.
-- Drop table TB_New note: The Age of JACK in the new HR table is updated to 99, and JOSN information is added.
Create table TB_New (Id INT, IdSeq VARCHAR (20), Name VARCHAR (200), Adress varchar (200), Age int)
-- Insert new test data
******* ***********************/
Insert into TB_New SELECT 1, 'china01', 'Jack', 'Kunming, 99 union all
SELECT 2, 'china02', 'Tom ', 'Yunnan Qujing', 82 union all
SELECT 3, 'china03', 'Alice ', 'Yunnan Lijiang', 60 union all
SELECT 4, 'china04 ', 'Jerry', 'Yunnan Zhaotong ', 26 union all
SELECT 5, 'china05 ', 'Terry', 'Chongqing Jiangbei ', 22 union all
SELECT 6, 'china06', 'job', 'Guangdong Shenzhen ', 25
------------- The following requirements are available now:
/*************************************** ******************
Over time, you need to update their related information in real time. assume that the input time for this age is January 1, 2014.
The system was upgraded on July 15, 2015. the entered information was named by the new rule. if the age is over 45 years old, the information will not be updated and will be considered as retired.
Old information has been encoded in the new system according to the new encoding rules, but this information needs to be reported to the general manager for display.
*/
/************************** Report information in the database ********* **********************************/
/* The information in the reported database is the data in the old system */
-- 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
/*************************************** **************************************** *******
Requirement: use the data provided in the new human resources management system to update the age in the reported information
*/
-- Create a ing view for associated updates
Go
-- Select * from Differrence_Data
Create view Differrence_Data
As
Select A. Age, B. Old_IdSeq from TB_New
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
------------------------------------------------------- Case 1: Update the age information corresponding to the old code.
If (select COUNT (*) from Differrence_Data)> 0)
------------------------- If data exists in the view Differrence_Data, update the old encoding information.
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
)
----------------------------------------------- Case 2: insert new data into the report table.
--- Insert new data to the report information table
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)
---------------------------------------------- Case 3: age information corresponding to the updated code
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 the newly added age change information
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
-- Execute the stored procedure
EXEC Get_NewInfo
-- Old human resources table
Select * from TB_old
-- New HR table
Select * from TB_New
-- Ing table
Select * from Mapping
-- Report on
Select * from Report_Rpt
--------------------------------------------
-- Delete a table statement
-- Drop table TB_old, TB_New, Mapping, Report_Rpt
------------------------------ Whether or not the corresponding data will be updated and inserted when the age of the old encoding and the age of the new encoding are tested and the new person information is added.
-------------------------------------- Change data in the new HR system for logic testing
Insert into TB_New SELECT 7, 'china07', 'Test 01', 'Yunnan Kunming ', 21 union all
SELECT 8, 'china08', 'Test 02', 'Kunming Yunnan ', 33
--------------- Modify new data for testing
Update TB_New set Age = '000000' where Id = '6'
--------------- Modify the age of old data for testing
Update TB_New set Age = '000000' where Id = '1'