How to Insert Data to Table Using INSERT INTO Statement

In the previous part of our SQL series, we discussed creating a table in SQL. Now, let’s move one step ahead and demonstrate the method to insert the required data in a table.

Inserting data into a database table is an important operation for database administrators and developers. The SQL INSERT INTO statement can be used for the mentioned purpose However, if you are not familiar with SQL, you might find it difficult.

In this post, we will go through the SQL INSERT INTO command usage in different case scenarios, talk about the relevant common errors, and cover best practices for maintaining database integrity and optimum performance.

1. What is SQL INSERT INTO Statement?

The SQL INSERT INTO statement permits you to insert new data or records into a table. It mainly defines where and what data needs to be inserted. You can utilize this statement for adding new records for the mentioned table in the database management systems.

SQL INSERT INTO Syntax

Follow the provided syntax for inserting or adding rows into your table.

INSERT INTO table_name (col1, col2, col3, ...)
VALUES (val1, val2, val3, ...);

Here:

• The “INSERT INTO” is the command defined for adding data.
• The “table_name” represents the table name where the rows need to be inserted.
• The “(col1, col2, col3)” indicates the column names.
• The “VALUES” keyword is utilized for specifying the set of values “(val1, val2, val3)” that you need to insert in the mentioned columns.

2. How to Use SQL INSERT INTO Statement

There could be various scenarios where the SQL INSERT INTO statement can be used.

However, the three major use cases are listed below:

• Insert a single row in a table.
• Insert multiple rows in a table.
• Insert data from one table to another.

Now, let’s demonstrate each of the mentioned use cases practically. For the corresponding purpose, we will utilize the same “authors” table, which we created earlier (as a demonstration in the SQL CREATE TABLE Statement article).

Before inserting the data, firstly, we will describe the structure of the authors table. This help in recalling the column names, data types, or constraints if applied:

DESC authors;

It can be observed that the authors table contains three columns, two of integer “INT” type and one of variable-length character “VARCHAR (50)” with size 50.

Currently, the authors table is empty. Move ahead to fill it out with some values.

2.1 Insert Row To a Table in SQL

For inserting a single row into the “authors” table, run the following Insert Into statement:

INSERT INTO authors (id, name, age) 
VALUES 
  (1, 'Ravi Saive', 28);

According to the given statement:

• In the column “id“, 1 will be inserted.
• In the column “name“, “Ravi Saive” will be inserted.
• In the column “age“, 28 will be inserted.
• Note that the double quotes are only utilized for specifying the string values.

Now, SELECT all "*" columns FROM the authors table for verification.

SELECT * FROM authors;

It can be observed that the row has been inserted successfully.

2.2 Insert Multiple Rows To a Table in SQL

Want to insert or add multiple rows at once? SQL INSERT INTO statement permits you to do so in the following way.

INSERT INTO authors (id, name, age) 
VALUES 
  (2, 'Sharqa Hameed', 25), 
  (3, 'Kennedy Brian', 32), 
  (4, 'Mead Naji', 29);

[/sql]
The given command inserts three rows into the authors table, with the specified values for the “id“, “name“, and “age” columns in each row.

To verify the performed insertion operation, run the provided SELECT statement.

SELECT * FROM authors;

As you can see, three new rows have been inserted.

2.3 Insert Data From One Table to Another in SQL

Sometimes, you may want to insert data from one table to another. This operation can be performed for various reasons, such as data consolidation (combining data from multiple tables into a single table), data migration (transferring data from one database to another), data backup and recovery, and data analysis.

For this example, we will create another table named “new_authors” by utilizing the CREATE TABLE statement.

CREATE TABLE new_authors(
  id INT, 
  name VARCHAR(50), 
  age INT
);

Now, we will run the following INSERT INTO statement.

INSERT INTO new_authors (id, name, age) 
SELECT 
  id, 
  name, 
  age 
FROM 
  authors 
WHERE 
  age >= 28;

According to the given statement:

• The “INSERT INTO” statement is defined to add the data into the newly created table “new_authors“.
• The columns where the data is going to be added are written within the parentheses as “id, name, age“.
• The “SELECT” statement defines the table “authors” and the relevant columns “id, name, age” from which the values will be fetched.
• The “WHERE” clause is utilized for filtering rows from the “authors” table based on this condition “age >= 28“.

Then, select all rows from the new_authors table.

SELECT * FROM new_authors;

It can be observed that only the rows where the authors‘ ages were greater than or equal to 28 have been inserted into the new_authors.

3. Common Errors When Using SQL INSERT INTO Statement

This section will discuss some of the common errors that can be encountered while utilizing the INSERT INTO statement and its solutions.

3.1 SQL ERROR 1146 (42S02)

While using the INSERT INTO statement, syntax-related errors can occur in the case you have not properly written the statement. For instance, misspelling the column or table name or specifying the incorrect syntax for the statement will resultantly show the relevant syntax error.

In this example, we will try to insert a new row. However, we have intentionally missed an “s” from the “authors” table name and specified it as “author“.

INSERT author (id, name, age) 
VALUES 
  (5, 'Marry', 31);

It can be seen that an error has been displayed stating that the author table does not exist in our current table.

ERROR 1146 (42S02): Table 'database1.author' doesn't exist

To resolve this error, write out the correct table name and then execute the same SQL INSERT INTO statement.

INSERT authors (id, name, age) 
VALUES 
  (5, 'Marry', 31);

3.2 SQL ERROR 1366 (22007)

Mismatched data type-related errors such as ERROR 1146 (42S02) occur when the data type of the mentioned column does not match the actual data type of the table’s column.

For instance, in our authors table, we have defined the INT type column named age. However, while adding a new row, let’s specify the age as a string rather than an integer and see what happens.

INSERT INTO authors (id, name, age) 
VALUES 
  (6, 'Alexa', 'thirty two');

As you can see, we have faced a data type mismatch error.

ERROR 1366 (22007): Incorrect integer value : 'thirty two' for column `database1`.`authors`.`age` at row 1

To fix the ERROR 1146 (42S02) error, now the age of the new author is defined as “32”.

INSERT INTO authors (id, name, age) 
VALUES 
  (6, 'Alexa', 32);

3.3 SQL ERROR 1062 (23000)

The errors or conflicts related to the Primary key such as ERROR 1062 (23000) occur when you try to add a row with a primary key value that is already present in the table.

To discuss the scenario further, firstly, we will create a new table named “editors” having the same structures which we have defined for the authors and new_authors. The only difference is that here we have specified “id” as the PRIMARY KEY of this editors table.

CREATE TABLE editors(
  id INT PRIMARY KEY, 
  name VARCHAR(50), 
  age INT
);

Then, insert multiple rows into the editors table.

INSERT INTO editors (id, name, age) 
VALUES 
  (1, 'Adam', 24), 
  (2, 'Bella', 27), 
  (3, 'Charlis', 38), 
  (4, 'Harry', 32);

View the table data for the verification of the insertion operation.

SELECT * FROM editors;

Now, try to insert a row with the id value 1 into the editors table that already has a row with the same id value.

INSERT INTO editors (id, name, age) 
VALUES 
  (1, 'Patrick', 30);

This will result in generating a primary key conflict error as follows.

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

To resolve the ERROR 1062 (23000) error, we will change the id value and again run the same command.

INSERT INTO editors (id, name, age) 
VALUES 
  (5, 'Patrick', 30);

4. Best Practices for Using the SQL INSERT INTO Statement

Here, we have enlisted some of the best practices for utilizing the SQL INSERT INTO statement:

• Validate data before performing the insertion operation.
• Mention the column names explicitly in the INSERT command.
• Use a single INSERT statement with multiple value sets for adding multiple rows at once.
• Take care of the order of the insert options for maintaining referential integrity (ensuring that the relationship between tables and their data is accurate and consistent).
• Monitor the INSERT operations performance and optimize them when required.

That was all about the usage of the SQL INSERT INTO statements.

Conclusion

The SQL INSERT INTO statement is utilized for adding new rows to the table of the current database. Therefore, it is crucial to understand its syntax for avoiding common errors and maintaining database integrity.

Moreover, by following best practices such as using parameterized queries and maintaining referential integrity, you can ensure data accuracy and optimize performance. This post discussed the method for using SQL INSERT INTO statement.

Keep learning and keep exploring GeekVeda and stay tuned for new content in this SQL series!