Lesson 1

In the last module, you learned how to select data, which is a very important step in learning how to use SQL Server. In this module, we are going to build upon this knowledge by using 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 avoid 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: Always use joins carefully to avoid 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 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: Ensure your subqueries are correlated properly and that they return scalar values for predicates where necessary.
✅ 4. Optimize your `INSERT`, `UPDATE`, and `DELETE` statements
🔹 General Optimization Guidelines:

• Use indexes on columns used in `WHERE`, `JOIN`, and `ON` clauses.
• Batch operations instead of row-by-row logic (use `MERGE`, `JOIN`, or `SELECT` subqueries).
• Avoid unnecessary triggers or cascading updates unless needed.
• Minimize locking and blocking by updating small 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;

Rules and constraints provide restriction information about what can go into a table. If an updated or inserted record violates a rule, that insertion or update will be rejected. In addition, a rule can be used to defi ne a restriction on a user-defi ned data type. Unlike constraints, rules are not bound to a particular table. Instead they are independent objects that can be bound to multiple tables or even to specific data types (which are, in turn, used in tables). Rules have been considered deprecated by Microsoft for several releases now. They should be considered for backward compatibility only, and you should avoid them in new development.

• two types of defaults.
  There are two types of defaults.
  1. There is the default that is an object unto itself, and
  2. the default that is not really an object, but rather metadata describing a particular column in a table (in much the same way that there are rules, which are objects, and constraints, which are not objects, but metadata).
  
  They serve the same purpose. If, when inserting a record, you do not provide the value of a column and that column has a default defi ned, a value will be inserted automatically as defi ned in the default.

In the next lesson, you will get an overview of the INSERT statement, which is used to insert data into your tables.