Manipulating Data in SQL Server

This section covers essential operations for adding, modifying, and deleting data within your SQL Server databases. Understanding these Data Manipulation Language (DML) statements is fundamental to effective database management.

INSERT Statements: Adding New Rows

The INSERT statement is used to add new records (rows) into a table. You can insert a single row or multiple rows at once.

Basic Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Inserting into Specific Columns

If you are providing values for all columns in the table, you can omit the column list. However, it is generally good practice to specify the columns to ensure clarity and prevent errors if the table structure changes.

INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES ('John', 'Doe', '2023-01-15');

Inserting Multiple Rows

INSERT INTO Products (ProductName, Price, Stock)
VALUES
('Laptop', 1200.00, 50),
('Keyboard', 75.50, 200),
('Mouse', 25.00, 300);

INSERT INTO Customers (CustomerID, CompanyName, ContactName, City)
VALUES (101, 'Acme Corporation', 'Jane Smith', 'New York');

UPDATE Statements: Modifying Existing Data

The UPDATE statement allows you to change existing records in a table. You must specify which rows to update using a WHERE clause.

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

If the WHERE clause is omitted, all rows in the table will be updated. Use caution!

Updating Specific Rows

UPDATE Products
SET Price = 1250.00
WHERE ProductName = 'Laptop';

Updating Multiple Columns

UPDATE Employees
SET Salary = Salary * 1.05, Department = 'Sales'
WHERE EmployeeID = 10;

UPDATE Products
SET Price = Price * 1.10
WHERE Category = 'Electronics';

DELETE Statements: Removing Data

The DELETE statement removes one or more rows from a table. Like UPDATE, it's crucial to use a WHERE clause to specify which rows to delete.

Basic Syntax

DELETE FROM table_name
WHERE condition;

If the WHERE clause is omitted, all rows in the table will be deleted. This is irreversible!

Deleting Specific Rows

DELETE FROM Customers
WHERE CustomerID = 101;

Deleting Multiple Rows

DELETE FROM Orders
WHERE OrderDate < '2022-01-01';

DELETE FROM Products
WHERE Stock = 0;

MERGE Statements: Conditional Inserts/Updates

The MERGE statement (also known as UPSERT) allows you to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. This is powerful for synchronizing data.

Basic Syntax

MERGE target_table AS T
USING source_table AS S
ON T.join_column = S.join_column
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, column3)
    VALUES (S.column1, S.column2, S.column3)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

MERGE Products AS P
USING StagingProducts AS SP
ON P.ProductID = SP.ProductID
WHEN MATCHED AND P.Price <> SP.Price THEN
    UPDATE SET P.Price = SP.Price, P.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (SP.ProductID, SP.ProductName, SP.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Key Considerations

• Transactions: Always wrap your DML operations within transactions to ensure data integrity. This allows you to rollback changes if an error occurs.
• WHERE Clause: Be extremely careful when using UPDATE and DELETE statements without a WHERE clause. Test your queries on a development environment first.
• Performance: For large datasets, consider the performance implications of your DML statements. Indexing relevant columns can significantly improve speed.
• Permissions: Ensure the user executing these statements has the necessary permissions on the tables.