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)
);