Database

Home / Topic / Database / INSERT Operations in SQL

INSERT Operations in SQL

After creating our tables in an SQL database, the next step is inserting data. The INSERT operation allows us to add data rows to a table. In this chapter, we will learn to insert data into a database using the INSERT INTO command, as well as some best practices for handling this process.

Inserting Data into a Table

The most basic operation we perform with a table is inserting new data. The INSERT INTO command is how new rows are added to an existing table. Below is an example of how to insert a record into a table called users.

sql

"Here we are inserting a new record into the users table, specifying values for the columns name and email. The value 'John Doe' is inserted into the name column, and 'john@example.com' into the email column."

Inserting Multiple Rows

Often, we need to insert multiple rows of data at once. Instead of making multiple individual insertions, we can use a single INSERT INTO statement to insert multiple rows at once.

sql

"This command inserts three records into the users table in a single statement. This way, we can reduce the overhead on the database when inserting multiple rows."

Inserting Data into All Columns

If we wish to insert data into all columns of a table, we can omit the column names in the INSERT INTO statement, as long as we provide the values in the same order in which the columns are defined.

sql

"In this example, we are inserting values for all columns in the users table. The first value is for the id column, followed by name, email, and finally created_at, where we use CURRENT_TIMESTAMP to record the current date and time."

Conditional Inserts with ON DUPLICATE KEY UPDATE

In some cases, we may want to update an existing record if there is one with a duplicate key. For this, we can use the ON DUPLICATE KEY UPDATE clause in combination with data insertion.

sql

"This command attempts to insert a new user with id equal to 1. If a record with that primary key already exists, then the email will be updated to 'john_new@example.com' instead of creating a new record."

Best Practices for Data Insertion

Use transactions when inserting large amounts of data. This ensures the operation is atomic and data does not remain incomplete in the event of a failure.
Validate data before inserting to ensure it meets database constraints (like unique primary keys and non-null fields).
Avoid mass insertions without an index in large tables as this can cause performance issues. It's recommended to create indexes before inserting large volumes of data.

sql

"Here we are using a transaction to ensure the insertion operation is atomic. The transaction begins with START TRANSACTION and ends with COMMIT, ensuring that data is inserted correctly."

Specifying Columns

In the previous example, we specified the columns name and email. If we do not specify all columns, it is necessary to omit those that have a default value or can be null. For example, we did not specify the id column because in the users table, this field is an auto-increment primary key.

sql

"Again, we are inserting a user named Bob with his email address. Since we did not specify a value for the id column, MySQL will generate a value automatically."

Inserting Multiple Rows

The INSERT command also allows inserting multiple rows at once, which is very useful when a table needs to be filled with several records.

sql

"Here we are inserting three new users in a single operation. The users' names are Charlie, David, and Emma, and we have provided their email addresses."

Inserting Data with AUTO_INCREMENT

When a table has a column defined as AUTO_INCREMENT, as in our example with the id column in the users table, it is not necessary to specify the value for that column when inserting a new record. MySQL will automatically assign the next available value for the primary key.

sql

"Here we are inserting a user named Frank. We do not need to specify the id value as MySQL will generate it automatically."

Inserting Data with Null Values

Sometimes it is necessary to insert a record that does not have values for all columns. In those cases, we can insert a NULL value in the columns that allow it.

sql

"This example shows how to insert a user named George without specifying an email address. Since the email column allows null values, we can insert the NULL value."

Inserting Data with Subqueries

Instead of providing the values manually, it is also possible to insert data into a table using the results of a query. This technique is called insert with subquery.

sql

"In this example, we are inserting into the archived_users table all users who were created before January 1, 2023. The subquery selects data from the users table and inserts it into archived_users."

Summary

In this chapter, we explored how to insert data into SQL tables using the INSERT INTO command, including inserting multiple rows and using the ON DUPLICATE KEY UPDATE clause. In the next chapter, we will learn to retrieve data from a database using the SELECT command, one of the most powerful in SQL.

Back to previous chapter

Mark as read and continue

Ask me anything