SQLserver using a ing table for data-related operations _ MySQL

Source: Internet
Author: User
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'

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.