Data Modification Language (DML) in SQL Server

Data Modification Language (DML)

Data Modification Language (DML) statements in SQL are used to modify the data stored in the database. Below is an overview of basic DML operations along with examples.

SQL Insert:

The INSERT INTO statement in SQL is used to add new records (rows) to a table. It allows you to specify the table name and the values you want to insert into the columns.

Syntax:

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

Example:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

This query inserts a new record into the Customers table with specific values for the CustomerID, FirstName, LastName, and Email columns.

If you want to insert values for all columns, you can omit the column list:

INSERT INTO Products
VALUES (101, 'Product A', 25.99, 50);

This query inserts a new record into the Products table without specifying column names. The values must be in the same order as the columns in the table.

You can also insert multiple records in a single INSERT INTO statement:

INSERT INTO Orders (CustomerID, ProductID, Quantity)
VALUES (1, 101, 3),
       (2, 102, 5),
       (1, 103, 2);

This query inserts three new records into the Orders table with different combinations of CustomerID, ProductID, and Quatity.

SQL Insert Into Select:

The INSERT INTO SELECT statement in SQL is used to insert data from one table into another table. It allows you to copy data from one table (or a result set of a query) and insert it into another table, potentially with modifications or filtering.

Syntax:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Example:

INSERT INTO NewCustomers (FirstName, LastName, Email)
SELECT FirstName, LastName, Email
FROM OldCustomers
WHERE RegistrationDate > '2022-01-01';

This query inserts data into the NewCustomers table from the OldCustomers table, but only for customers who registered after January 1, 2022.

SQL Update:

The UPDATE statement in SQL is used to modify existing records (rows) in a table. It allows you to change the values of one or more columns for one or more rows based on specified conditions.

Syntax:

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

Example:

UPDATE Customers
SET FirstName = 'Jane', LastName = 'Smith'
WHERE CustomerID = 1;

This query updates the FirstName and LastName columns of the customer with CustomerID equal to 1 in the Customers table.

To update all records in a table, you can omit the WHERE clause:

UPDATE Orders
SET Status = 'Shipped';

This query updates the Status column of all records in the Orders table to Shipped.

SQL Delete:

The DELETE statement in SQL is used to remove one or more records (rows) from a table based on specified conditions.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Customers
WHERE CustomerID = 1;

This query deletes the record with CustomerID equal to 1 from the Customers table.