Understanding the SQL INSERT INTO Statement: Examples

When it comes to managing data in a relational database, the SQL INSERT INTO statement plays a crucial role. It allows us to add new records to a table, ensuring the integrity and consistency of our data. In this blog post, we will explore the INSERT INTO statement, providing examples of common scenarios as well as more complex ones.

Basic Syntax

The basic syntax of the INSERT INTO statement is as follows:

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

The table_name refers to the name of the table where we want to insert data. The column1, column2, column3, ... represents the columns in the table where we want to insert values. And the value1, value2, value3, ... corresponds to the actual values we want to insert.

Example Scenarios

Scenario 1: Inserting a Single Record

Let's say we have a table called "Customers" with columns "CustomerID", "FirstName", and "LastName". To insert a single record into this table, we can use the following query:

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

Scenario 2: Inserting Multiple Records

Sometimes, we need to insert multiple records into a table at once. For instance, if we have a table called "Products" with columns "ProductID", "ProductName", and "Price", we can insert multiple records using a single INSERT INTO statement:

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1, 'Product A', 10.99),
       (2, 'Product B', 15.99),
       (3, 'Product C', 20.99);

Scenario 3: Inserting Data from Another Table

There are situations where we need to insert data from one table into another. For example, if we have a table called "NewCustomers" with the same structure as the "Customers" table, we can use the INSERT INTO statement with a SELECT query:

INSERT INTO Customers (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM NewCustomers;

Complex Scenarios

Scenario 4: Inserting Data with a Subquery

In more complex scenarios, we may need to insert data based on the result of a subquery. Let's consider a table called "Orders" with columns "OrderID", "CustomerID", and "TotalAmount". To insert a new order with the total amount equal to the average order amount for a specific customer, we can use the following query:

INSERT INTO Orders (OrderID, CustomerID, TotalAmount)
SELECT MAX(OrderID) + 1, CustomerID, AVG(TotalAmount)
FROM Orders
WHERE CustomerID = 1;

Scenario 5: Inserting Data with Conditional Logic

Conditional logic can also be used when inserting data. Suppose we have a table called "Employees" with columns "EmployeeID", "FirstName", "LastName", and "Salary". To insert a new employee with a higher salary based on their job title, we can use the following query:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName,
       CASE
           WHEN JobTitle = 'Manager' THEN Salary * 1.1
           WHEN JobTitle = 'Senior Developer' THEN Salary * 1.05
           ELSE Salary
       END
FROM NewEmployees;

These examples demonstrate the versatility and power of the SQL INSERT INTO statement. Whether it's a simple record insertion or a more complex scenario involving subqueries and conditional logic, the INSERT INTO statement allows us to efficiently manage our data.

Remember, understanding the INSERT INTO statement is essential for anyone working with databases, as it forms the foundation of data manipulation and maintenance.