SQL Insert

Inserting Data in Table

In the previous chapter, we created a table named persons in our demo database. Now it's time to add some data to our newly created table.

The INSERT INTO statement is used to insert new rows into a database table.

Syntax

The basic syntax for inserting data into a table is:

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

Here, column1, column2, etc., represent the names of the table columns, while value1, value2, and so on, represent the corresponding values for these columns.

Let's insert some records into the persons table.

Step 1: View Table Structure

Before adding records, it's a good idea to check the table structure. Execute the following command on the MySQL command line to display information about the columns in the persons table, such as column names, data types, constraints, etc.

mysql> DESCRIBE persons;

You can view the column information or structure of any table in MySQL and Oracle databases using the command DESCRIBE table_name;, while in SQL Server, you can use EXEC sp_columns table_name; (replace table_name with the actual table name).

Step 2: Adding Records to a Table

The following statement inserts a new row into the persons table.

INSERT INTO persons (name, birth_date, phone)
VALUES ('Peter Wilson', '1990-07-15', '0711-020361');

Did you notice, we didn't insert any value for the id field? This is because, as mentioned in the create table chapter, the id field was marked with the AUTO_INCREMENT flag, which instructs MySQL to automatically assign a value to this field if it is left unspecified.

Note: Non-numeric values such as strings and dates should always be enclosed in quotes, while numeric values should not be. Additionally, if your string contains quotes, you should escape them with a backslash like 'Let\'s go'.

In the same way, insert another row into the persons table as follows:

INSERT INTO persons (name, birth_date, phone)
VALUES ('Carrie Simpson', '1995-05-01', '0251-031259');

In a similar manner, insert another row into the persons table:

INSERT INTO persons (name, birth_date, phone)
VALUES ('Victoria Ashworth', '1996-10-17', '0695-346721');

Now, if you select the records from the persons table, the output will appear like this:

+----+--------------------+------------+-------------+
| id | name               | birth_date | phone       |
+----+--------------------+------------+-------------+
|  1 | Peter Wilson       | 1990-07-15 | 0711-020361 |
|  2 | Carrie Simpson     | 1995-05-01 | 0251-031259 |
|  3 | Victoria Ashworth  | 1996-10-17 | 0695-346721 |
+----+--------------------+------------+-------------+

We will explore the SQL statement for selecting records from tables in the next chapter.