Special Remove Historical Data for Purged Employees

Overview

This utility script performs comprehensive cleanup of historical data for employees that have been purged from the main EMPLOYEES table. It systematically removes all traces of purged employees from historical tables including EMPHISTORY, SUPEDITHIST, PAYDESHIST, and SCHEDHISTORY. This script is essential for maintaining data integrity and reducing database size after employee purge operations.

Parameters

This script has no input parameters - it automatically identifies and removes historical data for all employees not present in the current EMPLOYEES table.

Data Components

The script performs cleanup operations on four key historical tables:

EMPHISTORY - Employee historical records
SUPEDITHIST - Supervisor edit history
PAYDESHIST - Pay designation history
SCHEDHISTORY - Schedule history

For each table, it removes records where the filekey doesn't exist in the current EMPLOYEES table.

Output Format

The script returns deletion statistics for each processed historical table:

Column	Data Type	Description
TableName	nvarchar(255)	Name of historical table where deletions occurred
DeletedRows	int	Number of rows deleted from that table

Only tables with actual deletions (DeletedRows > 0) are displayed in the results.

Technical Implementation

The script uses:

Dynamic SQL generation for flexible table operations
NOT IN subquery to identify orphaned historical records
Cursor-based processing to iterate through historical tables
Hardcoded table list for specific historical table targeting
Output parameters for row count tracking
Results compilation for comprehensive reporting

Processing Logic

Historical Table Definition: Process predefined list of historical tables
Orphan Identification: Use NOT IN clause to find records for non-existent employees
Dynamic Deletion: Generate and execute DELETE statements for each historical table
Row Counting: Track number of rows deleted per table
Results Compilation: Collect and display deletion statistics

Historical Tables Processed

Table Name	Description	Purpose
EMPHISTORY	Employee historical records	Employee change history
SUPEDITHIST	Supervisor edit history	Historical supervisor edits
PAYDESHIST	Pay designation history	Pay designation change history
SCHEDHISTORY	Schedule history	Historical schedule information

Safety Features

Historical Focus: Only processes historical tables, not current data
Reference Integrity: Only removes records for non-existent employees
Comprehensive Scope: Ensures all historical traces are removed
Detailed Reporting: Provides full audit trail of deletions

Notes

Post-Purge Cleanup: Designed to run after employee purge operations
Historical Data Focus: Only affects historical tables, not current operational data
Comprehensive Removal: Ensures all historical traces of purged employees are removed
Database Size Reduction: Helps reduce database size by removing unnecessary historical data
Data Integrity: Maintains referential integrity by removing orphaned historical records
Audit Trail: Provides detailed reporting of all deletions performed
Safe Operation: Only removes records for employees that no longer exist
Performance Impact: May take time to process large historical datasets
RECOMMENDED: Always backup database before running this script
Run this script after completing employee purge operations
Consider the impact on historical reporting requirements
Verify that purged employees should have their history completely removed
Run during maintenance windows to minimize system impact
Monitor the results to ensure expected historical data was removed
Consider retention policies before removing historical data
Use this script as part of a comprehensive employee purge workflow

T-SQL

special__remove_historical_data_for_purged_emps.sql

--This script wipes out data for employees you've already deleted with a statement.

DELETE FROM EMPLOYEEHR WHERE OWNERID NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM ACCRUALACTIVITY WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM ACCRUALDATA WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM ARCHIVES WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM ARCTRANSACTIONS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM BENCOMP WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM BENHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM CARRYINTO WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPACTSTATUSHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPCORRACTIONHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPHOMEWGHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPINETACCESS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPLOYEEBENEFITS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPLOYEEFIELDS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPLOYEESCHASSIGN WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPLOYEESCHPREF WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPLOYEESKILLS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPPAYCLASSHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPPHOTO WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPRAISEHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPSTATUSHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EMPTRANRATES WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM EXCEPTIONHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM INCIDENTS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM JOBACTIVITY WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM JOBJOURNAL WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM JOBSTATEHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM JOBTRANS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM LEAVEREQ WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM MEMODATA WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM PAYDESHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM PAYDESHISTACT WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SCHDESHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SCHDESHISTACT WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SCHEDULES WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SHIFTEXCEPTIONS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SUPEDITHIST WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM SUPEDITS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM TRANSACTIONS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM TRIGGERACTIONS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)
DELETE FROM WORKGROUPTRANS WHERE FILEKEY NOT IN (SELECT FILEKEY FROM EMPLOYEES)

Content Inventory

Doc File: content/docs/utilities/special_remove_historical_data_for_purged_emps.mdx
SQL Script: SQL/utilities/special__remove_historical_data_for_purged_emps.sql

Special Remove Historical Data for Purged Employees

On this page