Welcome back to our SQL tutorial series! So far, we’ve covered various ways to retrieve and manipulate data using SQL. In this article, we’re going to explore the INSERT INTO statement, which is used to add new records to a table. Understanding how to use INSERT INTO is essential for populating your database with data.
Understanding the INSERT INTO Statement
The INSERT INTO statement is used to add new rows of data to a table. You can insert data into all columns of a table or specific columns, depending on your needs.
Basic Syntax of INSERT INTO
There are two main syntaxes for the INSERT INTO statement:
- Inserting into All Columns:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);- Inserting into Specific Columns:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);Let’s look at some practical examples to understand how to use the INSERT INTO statement effectively.
Example Table: employees
We’ll continue to use the employees table for our examples:
| id | name | position | salary |
|---|---|---|---|
| 1 | John Doe | Software Engineer | 70000.00 |
| 2 | Jane Smith | Data Analyst | 65000.00 |
| 3 | Emily Davis | Software Engineer | 70000.00 |
| 4 | Michael Brown | Project Manager | 80000.00 |
| 5 | Sarah Wilson | Data Analyst | 62000.00 |
Inserting Data into All Columns
If you want to insert a new employee into the employees table and provide values for all columns, you use:
INSERT INTO employees
VALUES (6, 'David Johnson', 'HR Manager', 75000.00);This command adds the following row to the employees table:
| id | name | position | salary |
|---|---|---|---|
| 6 | David Johnson | HR Manager | 75000.00 |
Inserting Data into Specific Columns
If you only want to insert data into specific columns, you specify the column names in the INSERT INTO statement:
INSERT INTO employees (name, position, salary)
VALUES ('Linda Taylor', 'Marketing Specialist', 68000.00);In this case, the id column will be automatically populated if it’s an auto-increment column. The new row added to the employees table might look like this:
| id | name | position | salary |
|---|---|---|---|
| 7 | Linda Taylor | Marketing Specialist | 68000.00 |
Inserting Multiple Rows
You can also insert multiple rows of data in a single INSERT INTO statement:
INSERT INTO employees (name, position, salary)
VALUES
('Mark Roberts', 'Sales Manager', 72000.00),
('Susan Green', 'Content Writer', 55000.00),
('Robert White', 'UX Designer', 70000.00);This command adds three new rows to the employees table:
| id | name | position | salary |
|---|---|---|---|
| 8 | Mark Roberts | Sales Manager | 72000.00 |
| 9 | Susan Green | Content Writer | 55000.00 |
| 10 | Robert White | UX Designer | 70000.00 |
Using INSERT INTO with SELECT
You can also use INSERT INTO to copy data from one table to another. For example, if you have another table named new_employees with the same structure as the employees table, you can copy data like this:
INSERT INTO new_employees (id, name, position, salary)
SELECT id, name, position, salary FROM employees;This command inserts all rows from the employees table into the new_employees table.
Conclusion
The INSERT INTO statement is a fundamental tool for adding new records to your SQL database. Whether you’re inserting data into all columns, specific columns, or multiple rows at once, understanding how to use INSERT INTO is crucial for populating and managing your database effectively.
In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!
