Write a Stored Procedure to Archive Old Employees

Create a Stored Procedure to Archive Old Employees

Write a MySQL query to create a stored procedure that moves old employees to an ArchivedEmployees table

Solution:

-- Create a stored procedure named `ArchiveOldEmployees`
DELIMITER //

-- Define the stored procedure `ArchiveOldEmployees`
CREATE PROCEDURE ArchiveOldEmployees()
BEGIN
    -- Insert old employees into the `ArchivedEmployees` table
    -- Select employees whose `LastWorkingDate` is more than 1 year ago
    INSERT INTO ArchivedEmployees (EmployeeID, Name, DepartmentID, Salary)
    SELECT EmployeeID, Name, DepartmentID, Salary 
    FROM Employees 
    WHERE LastWorkingDate < NOW() - INTERVAL 1 YEAR;

    -- Delete old employees from the `Employees` table
    -- Remove employees whose `LastWorkingDate` is more than 1 year ago
    DELETE FROM Employees 
    WHERE LastWorkingDate < NOW() - INTERVAL 1 YEAR;
END //

-- Reset the delimiter to the default `;`
DELIMITER ;
  

Explanation:

• Purpose of the Query:
• The goal is to automate the archiving of old employees using a stored procedure.
• Key Components:
• INSERT INTO ... SELECT: Moves old employees to the archive table.
• DELETE FROM: Removes old employees from the main table.
• Why use Stored Procedures?:
• Stored procedures encapsulate complex operations, making them reusable.
• Real-World Application:
• For example, in a HR system, you might use a stored procedure to archive old employees.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a stored procedure that moves old customers to an ArchivedCustomers table.
• Write a MySQL query to create a stored procedure that moves old orders to an ArchivedOrders table.
• Write a MySQL query to create a stored procedure that moves old products to an ArchivedProducts table.
• Write a MySQL query to create a stored procedure that moves old projects to an ArchivedProjects table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Create a Trigger to Enforce Maximum Salary.
Next MySQL Exercise: Call the Stored Procedure to Archive Old Employees.