Lesson 1

Insert, Update, Delete Queries in SQL Server

In the last module, you learned how to select data, a foundational skill for working with SQL Server. In this module, we build on that knowledge by exploring queries to insert, update, and delete data.

Learning Objectives
After completing this module, you will be able to:
1. Use literal values in your INSERT, UPDATE, and DELETE queries
2. Use INSERT and UPDATE queries to modify a table using values from another table
3. Write INSERT, UPDATE, and DELETE statements using subqueries
4. Optimize your INSERT, UPDATE, and DELETE statements

Here is a structured explanation for each of the listed tasks in SQL Server 2022, with examples and optimization guidance:
✅ 1. Use literal values in your `INSERT`, `UPDATE`, and `DELETE` queries
🔹 `INSERT` with Literal Values

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

🔹 `UPDATE` with Literal Values

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

🔹 `DELETE` with Literal Values

DELETE FROM Employees
WHERE EmployeeID = 101;

✅ Tip: Use parameterized queries in applications to prevent SQL injection and improve execution plan reuse.
✅ 2. Use `INSERT` and `UPDATE` queries to modify a table using values 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 joins are precise to avoid unintended Cartesian products in `UPDATE` and `INSERT` from SELECT operations.
✅ 3. Write `INSERT`, `UPDATE`, and `DELETE` statements using subqueries
🔹 `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: Verify that subqueries are properly correlated and return scalar values when used in predicates.
✅ 4. Optimize your `INSERT`, `UPDATE`, and `DELETE` statements
🔹 General Optimization Guidelines:

Create indexes on columns used in `WHERE`, `JOIN`, and `ON` clauses.
Use batch operations instead of row-by-row processing (e.g., `MERGE`, `JOIN`, or `SELECT` subqueries).
Avoid unnecessary triggers or cascading updates unless required.
Minimize locking by processing data in smaller batches.

-- Example: Deleting in batches
WHILE 1=1
BEGIN
    DELETE TOP (1000)
    FROM Logs
    WHERE LogDate < '2023-01-01';

    IF @@ROWCOUNT = 0 BREAK;
END

✅ Use `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 plans and statistics using:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Constraints in Data Modification

Constraints in SQL Server enforce rules on the data in tables, ensuring data integrity during `INSERT`, `UPDATE`, and `DELETE` operations. For example, a primary key constraint ensures each record is unique, while a foreign key constraint maintains relationships between tables. If a data modification violates a constraint, the operation is rejected.

Default Constraints
A default constraint automatically assigns a predefined value to a column when no value is provided during an `INSERT`. For example, if a `Status` column has a default value of 'Active', inserting a record without specifying `Status` will set it to 'Active'. This simplifies data entry and ensures consistency.
Example:
```
ALTER TABLE Employees
ADD CONSTRAINT DF_Status DEFAULT 'Active' FOR Status;

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (102, 'Jane', 'Smith');
-- Status will be set to 'Active' automatically
```

In the next lesson, you will explore advanced query techniques, such as combining multiple data modification operations and handling transactions.