Explore practical examples of Data Manipulation Language (DML) statements for SQL Server, covering common operations like inserting, updating, deleting, and querying data.

1. Inserting Data with INSERT

Learn how to add new rows to a table using the INSERT statement, including inserting single rows and multiple rows.

-- Inserting a single row
INSERT INTO Customers (CustomerID, CompanyName, ContactName)
VALUES (101, 'New Corp', 'Jane Doe');

-- Inserting multiple rows
INSERT INTO Products (ProductID, ProductName, UnitPrice)
VALUES
(78, 'Chai Latte', 18.00),
(79, 'Earl Grey Tea', 20.00);

2. Updating Data with UPDATE

Modify existing data in a table using the UPDATE statement. See how to update specific rows based on conditions.

-- Update a customer's contact name
UPDATE Customers
SET ContactName = 'John Smith'
WHERE CustomerID = 101;

-- Increase the price of a specific product
UPDATE Products
SET UnitPrice = UnitPrice * 1.10
WHERE ProductName = 'Chai Latte';

3. Deleting Data with DELETE

Remove rows from a table using the DELETE statement. Understand how to delete specific rows or all rows.

-- Delete a specific customer
DELETE FROM Customers
WHERE CustomerID = 101;

-- Delete all orders placed before a certain date
DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

4. Selecting Data with SELECT

Retrieve data from one or more tables using the SELECT statement. Cover basic selection, filtering, sorting, and joins.

-- Select all columns from the Products table
SELECT * FROM Products;

-- Select specific columns and filter results
SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE CustomerID = 5;

-- Join two tables to get customer and order information
SELECT C.CompanyName, O.OrderID, O.OrderDate
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE C.Country = 'USA';

5. Advanced DML Operations

Discover more complex DML scenarios, including using MERGE for upsert operations and managing transactions.

-- Using MERGE to insert or update a record
MERGE INTO Employees AS target
USING (VALUES (200, 'Alice Wonderland', 'HR')) AS source (EmployeeID, Name, Department)
ON target.EmployeeID = source.EmployeeID
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Department) VALUES (source.EmployeeID, source.Name, source.Department)
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, target.Department = source.Department;