Inserting Data with SQL INSERT INTO
Inserting data into a database is one of the most fundamental operations in SQL. The INSERT INTO statement is used to add new rows to a table. This chapter covers the basics of inserting data, including how to add data to all columns or specific columns, insert multiple rows at once, and more advanced use cases like inserting data from other tables.
By the end of this chapter, you will have a comprehensive understanding of how the INSERT INTO statement works, and you’ll be able to use it efficiently in your SQL queries. We’ll go through the step-by-step syntax, examples with code, and explanations of the output.
Basic Syntax of SQL INSERT INTO
The INSERT INTO statement in SQL allows you to insert data into a table by specifying the table name and the values for each column.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
table_name: The name of the table where you want to insert data.column1, column2, column3, ...: A list of the columns in the table where you will insert data.value1, value2, value3, ...: The corresponding values for the columns.
Example 1: Basic INSERT INTO
Let’s say we have an employees table with the following structure:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
Now, we want to insert a new employee into the employees table.
Query:
INSERT INTO employees (id, name, department, salary)
VALUES (3, 'David Green', 'Sales', 5500);
Output:
After running this query, the employees table will now contain:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | David Green | Sales | $5500 |
A new row for David Green has been successfully added to the table.
Inserting Data into Specific Columns
In some cases, you may not want to insert values into all columns. For instance, if your table has default values or auto-incrementing primary keys, you can omit those columns from the INSERT INTO statement.
Syntax:
INSERT INTO table_name (column1, column3, ...)
VALUES (value1, value3, ...);
Only the specified columns will have values inserted, and the others will take their default values or remain NULL.
Example 2: Inserting into Specific Columns
Let’s add a new employee but only provide values for the name and department columns, leaving the salary to its default (or NULL if no default is set).
Query:
INSERT INTO employees (name, department)
VALUES ('Mary White', 'HR');
Output:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | David Green | Sales | $5500 |
| 4 | Mary White | HR | N/A |
The salary for Mary White is NULL because no value was provided.
Inserting Multiple Rows
You can insert multiple rows into a table in a single query. This can save time and resources, especially when adding a large dataset.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1_1, value1_2, value1_3, ...),
(value2_1, value2_2, value2_3, ...),
...;
Example 3: Inserting Multiple Rows
Let’s insert two new employees at once:
Query:
INSERT INTO employees (name, department, salary)
VALUES
('Alice Brown', 'Marketing', 4500),
('Bob White', 'Finance', 4800);
Output:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | David Green | Sales | $5500 |
| 4 | Mary White | HR | N/A |
| 5 | Alice Brown | Marketing | $4500 |
| 6 | Bob White | Finance | $4800 |
Both Alice Brown and Bob White were added in a single query.
Inserting Data from Another Table
In SQL, it’s also possible to insert data into a table from the results of another query. This is particularly useful when you want to duplicate data or move data between tables.
Syntax:
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;
Example 4: Inserting Data from Another Table
Let’s say we have another table, temp_employees, with new employees who need to be moved to the main employees table:
| ID | Name | Department | Salary |
|---|---|---|---|
| 7 | Sarah Grey | IT | $6000 |
| 8 | Tom Black | Sales | $5200 |
Query:
INSERT INTO employees (name, department, salary)
SELECT name, department, salary
FROM temp_employees;
Output:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | David Green | Sales | $5500 |
| 4 | Mary White | HR | NULL |
| 5 | Alice Brown | Marketing | $4500 |
| 6 | Bob White | Finance | $4800 |
| 7 | Sarah Grey | IT | $6000 |
| 8 | Tom Black | Sales | $5200 |
The rows from temp_employees were successfully inserted into the employees table.
Handling Errors During Insertion
When inserting data, you might encounter errors like trying to insert duplicate values in a column that requires unique values (e.g., a primary key). You can handle such errors by using specific SQL clauses.
ON DUPLICATE KEY UPDATE
If a row with a duplicate key exists, you can update the existing row instead of raising an error.
Example 5: Handling Duplicate Keys
Suppose we try to insert a new employee with an id that already exists:
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'HR', 5200)
ON DUPLICATE KEY UPDATE salary = 5200;
In this case, the query would update John Doe‘s salary instead of inserting a duplicate row.
Using Default Values During Insertions
Many SQL tables have default values for certain columns. If you do not provide a value for a column during insertion, the default value will be used.
Example 6: Inserting Data with Defaults
Suppose the salary column has a default value of 4000. We can insert a row without specifying the salary:
Query:
INSERT INTO employees (name, department)
VALUES ('Eve Adams', 'Legal');
Output:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | David Green | Sales | $5500 |
| 4 | Mary White | HR | N/A |
| 5 | Alice Brown | Marketing | $4500 |
| 6 | Bob White | Finance | $4800 |
| 7 | Sarah Grey | IT | $6000 |
| 8 | Tom Black | Sales | $5200 |
| 9 | Eve Adams | Legal | $4000 |
Eve Adams was added with the default salary of 4000 since no salary was provided.
The INSERT INTO statement is a crucial part of SQL, allowing you to add new data to your tables. In this chapter, we covered how to insert data into all or specific columns, insert multiple rows at once, handle duplicate keys, and use default values. Happy coding !❤️