SQL INSERT Statement

The SQL INSERT statement is used to add new records to a database table. Whether adding a single row or multiple rows at once, the INSERT statement is essential for populating tables with data, which can later be queried and manipulated as needed.

In this tutorial, we will go through how to use the SQL INSERT statement to add data into MySQL tables with this detailed guide. We will cover syntax, examples for single and multiple row inserts, inserting specific columns, using default values, and copying data between tables.

Syntax of SQL INSERT Statement

The basic syntax of an SQL INSERT statement is:

</>
Copy
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Each part of this syntax has a specific purpose:

  • INSERT INTO: Specifies the table where the data will be inserted.
  • table_name: The name of the table to receive the new data.
  • (column1, column2, …): A comma-separated list of columns in the table where data is being inserted. The columns should match the values being inserted.
  • VALUES: Introduces the list of values to be inserted into the columns specified. Each value corresponds to a column.

The INSERT statement can also be used to add multiple rows with a single query by adding more sets of values, each separated by a comma.

Step-by-Step Guide with MySQL

Let’s walk through the steps to use the INSERT statement in MySQL. We’ll use MySQL 8.0 with MySQL Workbench and a sample students table with columns id, name, age, grade, and locality.

Pre Step: Creating the Database and Table

1. Open MySQL Workbench and create a new database by running the following SQL command:

</>
Copy
CREATE DATABASE school;

2. Use the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);
SQL INSERT Example - Creating Table in Database

Examples for Inserting Data into the Table

Once the table is created, you can start inserting data. Below are examples of various ways to insert records.

1. Inserting a Single Row

To insert a single row into the students table, specify the values for each column:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES ('Alice', 14, '8th', 'Northside');

This query inserts a record for a student named Alice, who is 14 years old, in the 8th grade, and from the locality “Northside”.

SQL INSERT - Inserting a Single Row

Let us see the contents of the table using SQL SELECT statement.

Table after SQL INSERT

2. Inserting Multiple Rows

You can insert multiple rows in a single INSERT statement by including additional sets of values:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES 
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

This query inserts four additional student records into the students table in one command. Each set of values represents a new row in the table.

SQL INSERT - Inserting Multiple Rows

Let us get the records in the table using SELECT statement.

Table after SQL INSERT

3. Inserting Data into Specific Columns

If you only want to insert values for specific columns and leave others with their default values (or as NULL), omit those columns in both the column list and the values list:

</>
Copy
INSERT INTO students (name, age)
VALUES ('Frank', 17);

This query inserts a student named Frank, who is 17 years old, without specifying the grade and locality values, which will remain NULL or default if specified in the table structure.

SQL INSERT - Inserting Data into Specific Columns

Let us get the records in the table using SELECT statement.

Table after SQL INSERT

4. Using DEFAULT Values in INSERT

If your table has default values set, you can use DEFAULT to use these values explicitly in your insert statement:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES ('Grace', 16, DEFAULT, DEFAULT);

This query inserts a student named Grace with age 16. For grade and locality, it uses the default values defined in the table, if available.

SQL INSERT - Using DEFAULT Values in INSERT

Let us get the records in the table using SELECT statement.

Table after SQL INSERT

FAQs for SQL INSERT

1. What is the purpose of the SQL INSERT statement?

The INSERT statement allows you to add new records into a database table. Each time you use INSERT, it creates a new row in the specified table with the given values.

2. Can I insert multiple rows with one SQL INSERT statement?

Yes, you can insert multiple rows by specifying additional sets of values in a single INSERT command, separated by commas.

3. What happens if I don’t provide a value for every column?

If a column is omitted in the INSERT statement, it will take the default value if specified or remain NULL if allowed by the table’s schema.

4. How do I insert data into specific columns only?

Specify the columns you want to insert data into in the INSERT INTO part of the statement, followed by only the values for those columns in VALUES.

5. Can I use SELECT to insert data from one table to another?

Yes, you can use INSERT INTO ... SELECT to copy data from one table to another. This is useful for duplicating rows or transferring data.