INSERT, UPDATE, DELETE in SQL Server

Data Manipulation Language (DML) statements let you add, change, and remove rows in your tables. SQL Server provides INSERT, UPDATE, and DELETE for these operations. Understanding these statements is critical for any application that writes data.

INSERT: Adding New Rows

The INSERT INTO statement adds one or more rows to a table. Always specify the column list explicitly so your query remains clear and resilient to schema changes.

-- Insert a single row
INSERT INTO Employees (FirstName, LastName, Email, Department)
VALUES ('Maria', 'Garcia', 'maria@example.com', 'Engineering');

-- Insert multiple rows
INSERT INTO Products (ProductName, Price, Stock)
VALUES
    ('Wireless Mouse', 29.99, 150),
    ('Mechanical Keyboard', 89.99, 75),
    ('USB-C Hub', 49.99, 200);

INSERT from a SELECT

You can insert data from another table or query result. This is useful for archiving data or populating summary tables.

INSERT INTO ArchivedOrders (OrderID, CustomerID, OrderDate, Total)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM Orders
WHERE OrderDate < '2023-01-01';

UPDATE: Modifying Existing Rows

The UPDATE statement changes values in existing rows. Always include a WHERE clause to target specific rows. An UPDATE without WHERE modifies every row in the table.

-- Update a single column
UPDATE Employees
SET Department = 'Product'
WHERE EmployeeID = 42;

-- Update multiple columns
UPDATE Products
SET Price = Price * 1.10,
    LastModified = GETDATE()
WHERE Category = 'Electronics';

UPDATE with JOIN

SQL Server lets you update a table based on values from another table using a FROM clause with a JOIN.

UPDATE e
SET e.ManagerName = m.FirstName + ' ' + m.LastName
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID;

DELETE: Removing Rows

The DELETE statement removes rows from a table. Like UPDATE, always use a WHERE clause to avoid deleting all data. For removing every row quickly, consider TRUNCATE TABLE instead.

-- Delete specific rows
DELETE FROM Orders
WHERE OrderStatus = 'Cancelled'
  AND OrderDate < '2023-01-01';

-- Delete with a subquery
DELETE FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID FROM Departments
    WHERE IsActive = 0
);

Using OUTPUT to See Affected Rows

SQL Server's OUTPUT clause returns the rows affected by an INSERT, UPDATE, or DELETE. This is useful for logging or confirming changes.

DELETE FROM Products
OUTPUT DELETED.ProductID, DELETED.ProductName
WHERE Stock = 0;

Always test DML statements with a SELECT first to verify the affected rows before running the actual modification. Wrapping changes in a transaction gives you the option to roll back if something goes wrong.