MSDN Documentation

Data Modification in SQL Server

This section details the fundamental SQL statements used to modify data within a SQL Server database. Understanding these operations is crucial for managing and maintaining the integrity of your data.

INSERT Statement

The INSERT statement is used to add new rows of data into a table. You can insert a single row, multiple rows, or the result of a query.

Syntax

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

Example: Inserting a Single Row

INSERT INTO Production.Products (ProductID, Name, ProductNumber, ListPrice)
VALUES (1001, 'Mountain Bike', 'BK-M101-L', 1299.99);

Example: Inserting Multiple Rows

INSERT INTO Production.Products (ProductID, Name, ProductNumber, ListPrice)
VALUES
    (1002, 'Road Bike', 'BK-R202-M', 899.50),
    (1003, 'Hybrid Bike', 'BK-H303-S', 650.00);

Example: Inserting Data from Another Table

INSERT INTO Sales.ArchiveSales
SELECT * FROM Sales.SalesOrderDetails
WHERE OrderDate < '2022-01-01';

UPDATE Statement

The UPDATE statement is used to modify existing records in a table. You can update specific rows based on a condition or update all rows in the table.

Syntax

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

Example: Updating a Specific Row

UPDATE Production.Products
SET ListPrice = 1399.99
WHERE ProductID = 1001;

Example: Updating Multiple Rows

UPDATE Production.Products
SET ListPrice = ListPrice * 1.05 -- Increase price by 5%
WHERE ProductSubcategoryID = 3;

Caution:

Always use a WHERE clause with UPDATE unless you intend to modify all records in the table. Omitting the WHERE clause can lead to unintended data loss or corruption.

DELETE Statement

The DELETE statement is used to remove rows from a table. You can delete specific rows based on a condition or delete all rows.

Syntax

DELETE FROM table_name
WHERE condition;

Example: Deleting a Specific Row

DELETE FROM Production.Products
WHERE ProductID = 1003;

Example: Deleting Multiple Rows

DELETE FROM Sales.SalesOrderDetails
WHERE OrderDate < '2021-01-01';

Caution:

Similar to UPDATE, omitting the WHERE clause in a DELETE statement will remove all rows from the table. Exercise extreme caution.

TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is a faster, more efficient way to remove all rows from a table compared to DELETE without a WHERE clause. It is a DDL (Data Definition Language) operation and cannot be rolled back easily.

Syntax

TRUNCATE TABLE table_name;

Example: Truncating a Table

TRUNCATE TABLE Log.ErrorLog;

Key Differences:

• DELETE is a DML (Data Manipulation Language) operation, logs each row deletion, and can be rolled back.
• TRUNCATE is a DDL operation, is minimally logged, is faster, deallocates space, and cannot be rolled back by default.