Update Employee with Random Names

Script: scripts/utilities/update_emp_w_random_name.sql

Overview

This utility script updates all employee records in the EMPLOYEES table with randomly assigned first and last names. It's designed for data anonymization purposes, typically used in development, testing, or demo environments where real employee names need to be obscured while maintaining data structure and relationships.

Parameters

This script does not require input parameters and processes all records in the EMPLOYEES table.

Data Components

The script contains two main components:

Last Names Pool - 50 common American surnames
First Names Pool - 90 common American first names (mix of traditionally male and female names)

The script uses a Common Table Expression (CTE) to:

Assign row numbers to all employee records
Randomly select names from predefined lists
Update both firstname and lastname fields simultaneously

Output Format

This script performs UPDATE operations and does not return a result set. The affected columns are:

Column	Data Type	Description
lastname	varchar	Updated with randomly selected surname
firstname	varchar	Updated with randomly selected first name

Technical Implementation

The script uses:

Common Table Expression (CTE) with ROW_NUMBER() for record processing
VALUES clauses to define name pools
NEWID() function for random selection
TOP 1 with ORDER BY NEWID() for random name picking
Correlated subqueries to ensure each employee gets a unique random combination

Notes

⚠️ WARNING: This script modifies ALL employee records - use with extreme caution
Data Anonymization: Designed for development/testing environments only
Backup Recommended: Always backup original data before running
Random Assignment: Each execution will produce different name combinations
Name Pool: Contains 50 last names and 90 first names for variety
Production Warning: NEVER run this script in production environments
Testing Purpose: Ideal for creating anonymized datasets for development
Reversible: Only if you have backed up the original data
Consider adding WHERE clauses to limit scope if needed for specific testing scenarios

T-SQL

update_emp_w_random_name.sql

WITH
    EmployeeNames
    AS
    (
        SELECT *,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
        FROM EMPLOYEES
    )

UPDATE EmployeeNames
SET lastname = (
        SELECT TOP 1
    name
FROM (
            VALUES
        ('Smith'),
        ('Johnson'),
        ('Williams'),
        ('Jones'),
        ('Brown'),
        ('Davis'),
        ('Miller'),
        ('Wilson'),
        ('Moore'),
        ('Taylor'),
        ('Anderson'),
        ('Thomas'),
        ('Jackson'),
        ('White'),
        ('Harris'),
        ('Martin'),
        ('Thompson'),
        ('Garcia'),
        ('Martinez'),
        ('Robinson'),
        ('Clark'),
        ('Rodriguez'),
        ('Lewis'),
        ('Lee'),
        ('Walker'),
        ('Hall'),
        ('Allen'),
        ('Young'),
        ('Hernandez'),
        ('King'),
        ('Scott'),
        ('Green'),
        ('Adams'),
        ('Baker'),
        ('Gonzalez'),
        ('Nelson'),
        ('Carter'),
        ('Mitchell'),
        ('Perez'),
        ('Roberts'),
        ('Turner'),
        ('Phillips'),
        ('Campbell'),
        ('Parker'),
        ('Evans'),
        ('Edwards'),
        ('Collins'),
        ('Stewart'),
        ('Morris'),
        ('Nguyen')
        ) AS LastNames(name)
WHERE RowNum = EmployeeNames.RowNum
ORDER BY NEWID()
    ),
    firstname = (
        SELECT TOP 1
    name
FROM (
            VALUES
        -- Male Names
        ('James'),
        ('John'),
        ('Robert'),
        ('Michael'),
        ('William'),
        ('David'),
        ('Richard'),
        ('Joseph'),
        ('Charles'),
        ('Thomas'),
        ('Daniel'),
        ('Matthew'),
        ('Anthony'),
        ('Donald'),
        ('Mark'),
        ('Paul'),
        ('Steven'),
        ('Andrew'),
        ('Kenneth'),
        ('Joshua'),
        ('Kevin'),
        ('Brian'),
        ('George'),
        ('Edward'),
        ('Ronald'),
        ('Timothy'),
        ('Jason'),
        ('Jeffrey'),
        ('Ryan'),
        ('Jacob'),
        ('Christopher'),
        ('Frank'),
        ('Eric'),
        ('Stephen'),
        ('Larry'),
        ('Raymond'),
        ('Gregory'),
        ('Roger'),
        ('Benjamin'),
        ('Patrick'),
        ('Terry'),
        ('Carl'),
        ('Arthur'),
        ('Jerry'),
        ('Dennis'),
        ('Roy'),
        ('Henry'),
        ('Douglas'),
        ('Jose'),
        ('Adam'),
        -- Female Names
        ('Mary'),
        ('Jennifer'),
        ('Linda'),
        ('Patricia'),
        ('Elizabeth'),
        ('Susan'),
        ('Jessica'),
        ('Sarah'),
        ('Karen'),
        ('Nancy'),
        ('Lisa'),
        ('Betty'),
        ('Dorothy'),
        ('Sandra'),
        ('Ashley'),
        ('Kimberly'),
        ('Donna'),
        ('Emily'),
        ('Michelle'),
        ('Carol'),
        ('Amanda'),
        ('Melissa'),
        ('Deborah'),
        ('Stephanie'),
        ('Rebecca'),
        ('Laura'),
        ('Sharon'),
        ('Cynthia'),
        ('Kathleen'),
        ('Amy'),
        ('Shirley'),
        ('Angela'),
        ('Helen'),
        ('Anna'),
        ('Brenda'),
        ('Pamela'),
        ('Nicole'),
        ('Christine'),
        ('Martha'),
        ('Janet')
        ) AS FirstNames(name)
WHERE RowNum = EmployeeNames.RowNum
ORDER BY NEWID()
    );

Content Inventory

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

Update Employee with Random Names

On this page