"SQL Server Database Engine Error while inserting and updating employee records in test environment"

I'm running a SQL Server test script that inserts employee data and performs several operations.

Some statements work, but a few lines throw different errors — duplicate keys, missing columns, and invalid joins.

Below is my script. Can anyone explain which parts cause these specific SQL errors and why they occur?

-- Create a fresh database for testing

CREATE DATABASE HR_TestData;

GO

USE HR_TestData;

GO

-- Create an Employee master table

CREATE TABLE EmployeeMaster (

EmpCode BIGINT PRIMARY KEY,

FullName NVARCHAR(100),

Department NVARCHAR(50),

Email NVARCHAR(200),

CreatedOn DATETIME DEFAULT GETDATE()

);

GO

-- Insert one base record

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18883196619, 'Alice Johnson', 'Finance', 'alice.j@corp.local');

GO

-- Insert a few more for testing (valid)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18883196619, 'Robert Lee', 'IT', 'robert.l@corp.local'),

   (18883196619, 'Maria Garcia', 'HR', 'maria.g@corp.local');

GO

-- Duplicate insert (Error: Violation of PRIMARY KEY constraint)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18883196619, 'Alice Clone', 'Finance', 'duplicate@corp.local');

GO

-- Typo in column name (Error: Invalid column)

INSERT INTO EmployeeMaster (EmpID, FullName, Department, Email)

VALUES (18883196619, 'Chris Nolan', 'Admin', 'chris.n@corp.local');

GO

-- Null insert into NOT NULL column (Error)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department)

VALUES (NULL, 'Mark Ray', 'Legal');

GO

-- Join with a non-existing table (Error)

SELECT e.EmpCode, e.FullName, p.ProjectName

FROM EmployeeMaster e

JOIN ProjectAssignments p ON e.EmpCode = p.EmpCode;

GO

-- Add a few updates and checks

UPDATE EmployeeMaster

SET Department = 'Operations'

WHERE FullName LIKE '%Maria%';

GO

-- Invalid column again (Error)

UPDATE EmployeeMaster

SET Dept = 'Finance'

WHERE EmpCode = 18883196619;

GO

-- Simple select to verify data

SELECT EmpCode, FullName, Department, Email

FROM EmployeeMaster;

GO

-- Mistyped table name (Error: Invalid object name)

SELECT * FROM EmployeeMasters;

GO

-- Aggregation test (works fine)

SELECT Department, COUNT(*) AS TotalEmployees

FROM EmployeeMaster

GROUP BY Department;

GO

error

Msg 2627, Level 14, State 1

Violation of PRIMARY KEY constraint 'PK__Employee__...'. Cannot insert duplicate key.

The duplicate key value is (12345678901).

Msg 207, Level 16, State 1

Invalid column name 'EmpID'.

Msg 515, Level 16, State 2

Cannot insert the value NULL into column 'EmpCode'; column does not allow nulls.

Msg 208, Level 16, State 1

Invalid object name 'ProjectAssignments'.

Msg 207, Level 16, State 1

Invalid column name 'Dept'.

Msg 208, Level 16, State 1

Invalid object name 'EmployeeMasters'.

================================================

I'm running a SQL Server test script that inserts employee data and performs several operations.

Some statements work, but a few lines throw different errors — duplicate keys, missing columns, and invalid joins.

Below is my script. Can anyone explain which parts cause these specific SQL errors and why they occur?

-- Create a fresh database for testing

CREATE DATABASE HR_TestData;

GO

USE HR_TestData;

GO

-- Create an Employee master table

CREATE TABLE EmployeeMaster (

EmpCode BIGINT PRIMARY KEY,

FullName NVARCHAR(100),

Department NVARCHAR(50),

Email NVARCHAR(200),

CreatedOn DATETIME DEFAULT GETDATE()

);

GO

-- Insert one base record

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18882741273, 'Alice Johnson', 'Finance', 'alice.j@corp.local');

GO

-- Insert a few more for testing (valid)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18882741273, 'Robert Lee', 'IT', 'robert.l@corp.local'),

   (18882741273, 'Maria Garcia', 'HR', 'maria.g@corp.local');

GO

-- Duplicate insert (Error: Violation of PRIMARY KEY constraint)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department, Email)

VALUES (18882741273, 'Alice Clone', 'Finance', 'duplicate@corp.local');

GO

-- Typo in column name (Error: Invalid column)

INSERT INTO EmployeeMaster (EmpID, FullName, Department, Email)

VALUES (18882741273, 'Chris Nolan', 'Admin', 'chris.n@corp.local');

GO

-- Null insert into NOT NULL column (Error)

INSERT INTO EmployeeMaster (EmpCode, FullName, Department)

VALUES (NULL, 'Mark Ray', 'Legal');

GO

-- Join with a non-existing table (Error)

SELECT e.EmpCode, e.FullName, p.ProjectName

FROM EmployeeMaster e

JOIN ProjectAssignments p ON e.EmpCode = p.EmpCode;

GO

-- Add a few updates and checks

UPDATE EmployeeMaster

SET Department = 'Operations'

WHERE FullName LIKE '%Maria%';

GO

-- Invalid column again (Error)

UPDATE EmployeeMaster

SET Dept = 'Finance'

WHERE EmpCode = 18882741273;

GO

-- Simple select to verify data

SELECT EmpCode, FullName, Department, Email

FROM EmployeeMaster;

GO

-- Mistyped table name (Error: Invalid object name)

SELECT * FROM EmployeeMasters;

GO

-- Aggregation test (works fine)

SELECT Department, COUNT(*) AS TotalEmployees

FROM EmployeeMaster

GROUP BY Department;

GO error Msg 2627, Level 14, State 1

Violation of PRIMARY KEY constraint 'PK__Employee__...'. Cannot insert duplicate key.

The duplicate key value is (18882741273).

Msg 207, Level 16, State 1

Invalid column name 'EmpID'.

Msg 515, Level 16, State 2

Cannot insert the value NULL into column 'EmpCode'; column does not allow nulls.

Msg 208, Level 16, State 1

Invalid object name 'ProjectAssignments'.

Msg 207, Level 16, State 1

Invalid column name 'Dept'.

Msg 208, Level 16, State 1

Invalid object name 'EmployeeMasters'.