Data Modification Queries in SQL Server 2022

After learning how to retrieve data using SELECT statements, the next essential step in SQL Server is to understand how to modify that data efficiently and safely. This lesson covers how to insert, update, and delete records, including methods for optimizing these operations in SQL Server 2022.

Learning Objectives

1. Write INSERT, UPDATE, and DELETE statements using literal values.
2. Use data from one table to modify another.
3. Incorporate subqueries into modification statements.
4. Optimize modification queries for performance and data integrity.

1. Using Literal Values in Data Modification Queries

INSERT Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'John', 'Doe', 60000);

UPDATE Example:

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

DELETE Example:

DELETE FROM Employees
WHERE EmployeeID = 101;

Tip: Always use parameterized queries within applications to prevent SQL injection attacks and to reuse execution plans efficiently.

2. Using Data from Another Table

INSERT from Another Table:

INSERT INTO HighEarners (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 100000;

UPDATE from Another Table:

UPDATE E
SET E.Salary = B.NewSalary
FROM Employees E
JOIN BonusTable B ON E.EmployeeID = B.EmployeeID;

Tip: Ensure JOIN conditions are properly defined to prevent unintended cross joins and data corruption.

3. Using Subqueries in Data Modification

INSERT Using Subquery:

INSERT INTO TopPerformers (EmployeeID, FullName)
SELECT EmployeeID, FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID 
    FROM Performance 
    WHERE Rating = 'A'
);

UPDATE Using Subquery:

UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Engineering'
);

DELETE Using Subquery:

DELETE FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Resignations
    WHERE ResignDate < GETDATE()
);

Tip: Subqueries must return a single scalar value when used in predicates. For multi-row results, use IN or EXISTS appropriately.

4. Optimizing Data Modification Queries

• Create indexes on frequently filtered or joined columns.
• Use batch operations (e.g., MERGE) instead of row-by-row updates.
• Avoid unnecessary triggers or cascading updates unless required for business logic.
• Process data in smaller batches to reduce locking and blocking.

Example: Deleting Records in Batches

WHILE 1=1
BEGIN
    DELETE TOP (1000)
    FROM Logs
    WHERE LogDate < '2023-01-01';

    IF @@ROWCOUNT = 0 BREAK;
END

Using MERGE for Complex Logic:

MERGE INTO Employees AS Target
USING NewSalaries AS Source
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN 
    UPDATE SET Target.Salary = Source.NewSalary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Salary)
    VALUES (Source.EmployeeID, Source.NewSalary);

Check Execution Performance:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

5. Maintaining Data Integrity with Constraints

Constraints ensure data validity during insert, update, and delete operations. SQL Server enforces these rules automatically to prevent invalid data from being stored.

• Primary Key: Guarantees each record is unique.
• Foreign Key: Maintains referential integrity between related tables.
• Default Constraint: Assigns a predefined value if none is provided.

Example: Default Constraint

ALTER TABLE Employees
ADD CONSTRAINT DF_Status DEFAULT 'Active' FOR Status;

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (102, 'Jane', 'Smith');
-- Status defaults to 'Active'

By applying these principles, you can create robust, efficient, and secure modification queries that scale well in SQL Server 2022 environments. The next lesson introduces transactions and advanced multi-table modification techniques.