SQL Insert Into Statement

The INSERT INTO statement in SQL is used to insert new rows of data into a table. It is one of the most important statements in SQL and is used extensively in database management. This statement can be used to add a single record or multiple records into a table. You may want to check out our articles on SQL SELECT, WHERE, and ORDER BY clauses to get a better understanding of how to retrieve and sort data from a database.

Syntax of SQL Insert Into Statement

The basic syntax of INSERT INTO statement is as follows:

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

In this syntax, table_name is the name of the table into which you want to insert the data. The column1, column2, column3, ... specify the columns of the table you want to insert data into. The value1, value2, value3, ... specify the values to be inserted into the corresponding columns.

You can also insert data into all the columns of a table using the following syntax:

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

Examples

Here are some examples of how to use the INSERT INTO statement in SQL:

Inserting a Single Record

Suppose we have a table named employees with the following columns:

• employee_id (integer)
• first_name (string)
• last_name (string)
• email (string)
• hire_date (date)

We can insert a new record into the employees table using the following SQL statement:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', '[email protected]', '2022-04-22');

This will add a new row to the employees table with the specified values.

Inserting Multiple Records

You can also use the INSERT INTO statement to insert multiple rows of data into a table. For example:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1002, 'Jane', 'Smith', '[email protected]', '2022-04-23'),
       (1003, 'Mike', 'Brown', '[email protected]', '2022-04-24'),
       (1004, 'Sara', 'Johnson', '[email protected]', '2022-04-25');

This will add three new rows to the employees table with the specified values.

SQL INSERT INTO SELECT statement

The syntax for the SQL INSERT INTO SELECT statement is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table_name
WHERE condition;

Here’s an example of using the SQL INSERT INTO SELECT statement to insert data from the “employees” table into the “new_employees” table:

INSERT INTO new_employees (name, age, salary)
SELECT name, age, salary
FROM employees
WHERE age < 30;

In this example, we’re inserting data for employees who are under 30 years old from the “employees” table into the “new_employees” table.

It’s important to note that the columns in the target table must match the columns in the SELECT statement, and the data types must also match. If they don’t match, you’ll get an error message.

SQL INSERT IF NOT EXISTS

Sometimes we may want to insert a new record into a table only if it doesn’t already exist. We can use the INSERT INTO ... SELECT statement with a subquery and a WHERE NOT EXISTS clause to achieve this. The syntax is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
SELECT value1, value2, value3, ...
WHERE NOT EXISTS (SELECT * FROM table_name WHERE condition);

The subquery selects the values to be inserted, and the WHERE NOT EXISTS clause checks if there are any records in the table that satisfy the condition. If no records satisfy the condition, the new record is inserted.

Example

Let’s consider a scenario where we want to insert a new record into the employees table only if an employee with the same id doesn’t already exist:

INSERT INTO employees (id, name, age, salary)
SELECT 102, 'Jane Smith', 30, 60000
WHERE NOT EXISTS (SELECT * FROM employees WHERE id = 102);

If there is no record in the employees table with an id of 102, this statement inserts a new record with the specified values. Otherwise, nothing is inserted.

SQL INSERT DATE

In SQL, we can also insert dates into a table using the INSERT INTO statement. We can either use a string in the format ‘YYYY-MM-DD’ or a date function like CURRENT_DATE() or SYSDATE() to insert the current date.

Example

Let’s consider an example where we want to insert the current date into the orders table:

INSERT INTO orders (order_date, amount)
VALUES (CURRENT_DATE(), 1000);

This statement inserts a new record into the orders table with the current date and an amount of 1000.

That’s a basic introduction to the SQL INSERT INTO statement. We’ve also covered how to use the INSERT INTO ... SELECT statement with a WHERE NOT EXISTS clause to insert records only if they don’t already exist, as well as how to insert dates into a table.

Practice Tasks

Beginner

Suppose you are a small business owner and you need to keep track of your inventory. Create a table named “inventory” with the following columns: “product_id”, “product_name”, “price”, and “quantity”. Insert the following data into the table:

Insert a new record into the “inventory” table for a new product. The product ID is 4, the product name is “Sunglasses”, the price is 25, and the quantity is 10.

Intermediate

Insert multiple records into the expenses table with the following data:

• id: 124, date: ‘2022-05-16’, description: ‘Gasoline’, category: ‘Transportation’, amount: 30.00
• id: 125, date: ‘2022-05-17’, description: ‘Dinner’, category: ‘Food’, amount: 80.00

Advanced

Customers Table

Orders Table

You have two existing tables named customers and orders. The customers table has columns customer_id, first_name, last_name, and email. The orders table has columns order_id, customer_id, order_date, product_name, and quantity. Write an INSERT INTO SELECT statement to insert data into a new table named order_summary that summarizes customer orders. The order_summary table should have columns customer_id, customer_name, total_orders, and total_quantity.

Conclusion

The SQL INSERT INTO statement is a powerful tool for adding new data to a table, and the SQL INSERT INTO SELECT statement is a useful way to insert data from one table into another. By mastering these statements, you’ll be able to manipulate data within your database with ease.

If you want to learn more about SQL, be sure to check out our articles on the SQL UPDATE and DELETE statements. The UPDATE statement is used to modify existing data in a table, while the DELETE statement is used to remove data from a table. By understanding these statements, you can gain a comprehensive understanding of how to manage and manipulate data in SQL.