Lesson 1

In the previous module, you focused on SELECT statements and learned how to retrieve data from one or more tables. In this module, you will build on that foundation by learning how to change data in your database using the three core Data Manipulation Language (DML) statements in SQL Server: INSERT, UPDATE, and DELETE.

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. Apply basic performance and safety practices when writing DML statements in SQL Server.

1. Using Literal Values in INSERT, UPDATE, and DELETE

The most direct form of DML uses literal values in the statement. This is useful for simple changes, test data, and one-off data corrections.

INSERT with Literal Values

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

UPDATE with Literal Values

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

DELETE with Literal Values

DELETE FROM Employees
WHERE EmployeeID = 101;

In application code, you should avoid building these statements with string concatenation. Instead, use parameterized queries (e.g., SqlCommand with parameters) to prevent SQL injection and to improve plan reuse.

2. Using Values from Another Table

In real systems, you often move or transform data between tables. SQL Server allows you to populate or update a table directly from another table using a combination of INSERT, UPDATE, and JOIN or SELECT.

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   AS E
JOIN    BonusTable  AS B
        ON E.EmployeeID = B.EmployeeID;

Always verify your join condition when using UPDATE from another table. An incorrect join can update many more rows than intended or even all rows in the target table.

3. Using Subqueries in INSERT, UPDATE, and DELETE

Subqueries allow you to express DML logic that depends on values in other tables or filtered sets of rows.

INSERT Using a Subquery

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

UPDATE Using a Subquery

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

DELETE Using a Subquery

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

When you use subqueries in WHERE clauses, ensure that:

• The subquery returns a single value when used in a scalar context (e.g., = (SELECT ...)).
• You use IN or EXISTS when the subquery can return multiple rows.

4. Optimizing and Safeguarding DML Statements

Although this module introduces the basics of DML, it is important to begin with sound habits that scale to production workloads.

General Optimization Guidelines

• Create and maintain indexes on columns used in WHERE, JOIN, and ON clauses.
• Prefer set-based operations over row-by-row logic.
• Avoid unnecessary triggers or cascading updates unless there is a clear business requirement.
• Update or delete in smaller batches to reduce locking and blocking in busy systems.

Example: Deleting in Batches

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

    IF @@ROWCOUNT = 0 BREAK;
END;

This pattern removes old rows gradually, which is safer for large tables than one massive DELETE statement.

Using MERGE for More Complex Logic

The MERGE statement can combine insert and update logic in a single command. It is powerful but should be used carefully and tested thoroughly.

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);

For many scenarios, separate INSERT and UPDATE statements are easier to understand and debug. Use whichever approach matches your team’s standards and error-handling strategy.

Measuring the Cost of Your Queries

SQL Server provides tools to help you evaluate the impact of your DML statements:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run your DML statement(s) here.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

For deeper analysis, you can also inspect the execution plan in SQL Server Management Studio (SSMS) to see how indexes and joins are used.

When inserting, updating, or deleting data, SQL Server enforces business rules through constraints and default definitions. If a DML operation violates a rule, the change is rejected.

Historically, SQL Server supported separate objects called rules and standalone default objects. These have been deprecated for several releases and are kept only for backward compatibility. In modern development, you should use:

• CHECK constraints to enforce valid ranges and allowed values.
• FOREIGN KEY constraints to maintain referential integrity.
• DEFAULT constraints defined directly on columns to supply default values.

Defaults serve a simple but important purpose: if you insert a row and omit a column that has a default defined, SQL Server automatically provides the default value.

In the next lesson, you will take a closer look at the INSERT statement and see how these ideas combine when adding data to your tables.