SQL INSERT Statement

Introduction

SQL is a go to tool when working with data, and so is important for anybody working with data. SQL has a wide range of functions for creating, manipulating, and managing databases, making it all so easy. In this article, we will explore one of the fundamental functions in SQL – the INSERT statement. This statement handles the addition of new records to tables, which helps us to keep our database up to date. We will be covering the basic syntaxes of INSERT and some examples to know more about its use cases. Now let’s get into our article.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Understand what the INSERT function in SQL does.
• Learn the syntax of INSERT statements.
• Learn how to implement the INSERT function in various use cases.

What is INSERT in SQL?

The main functionality of INSERT statement is to add new records into the table. We can either do it one by one or multiple records at once. INSERT statement is the go-to tool when it comes to adding records to the table. We should understand how to use this statement in order to manage our data well in any SQL-based database. There are many ways to insert data into the table. Knowing different ways and options in INSERT statement is very important. Now that we know about INSERT statements lets know them with some examples.

Learn More: 24 Commonly Used SQL Functions for Data Analysis Tasks

Sample Table Structure

Let’s understand the functioning of INSERT in SQL through a live example. For this, we first need to create a sample table. Here’s how that’s done:

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Now we are ready to test out our various INSERT statements.

Basic INSERT Statement

The basic syntax used in SQL to INSERT a single record into the table is:

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

Altering this command according to our table. We will be specifying the table_name and columns present in the table. Then pass the set of values for those columns.

INSERT INTO employees (first_name, last_name, department, salary) 
VALUES ('Alice', 'Brown', 'Finance', 80000);

Here we can see that the order of columns is matched with the order of values passed i.e first_name = “Alice”, last_name = “Brown”, department = “Finance” and salary = 80000. Hence we have to maintain order.

Inserting Multiple Rows

INSERT INTO employees (col1, col2, etc) 
VALUES 
(value_set1),
(value_set2),
(value_set3), 
etc;

We can insert multiple records in a single statement by providing multiple value sets to the INSERT statement.

INSERT INTO employees (first_name, last_name, department, salary) 
VALUES 
('Bob', 'White', 'Sales', 58000),
('Carol', 'Green', 'HR', 54000),
('David', 'Black', 'IT', 75000);

We can see that now we have 4 records in the result set. Note that we should maintain the order of values else you may get errors when inserting.

INSERT INTO … SELECT

INSERT INTO table_name1 (col1, col2, col3, etc)
SELECT column1, column2, column3, etc
FROM table_name2;

We can also INSERT records of other tables using the SELECT statement. This method is very useful when copying or transferring data from one table to another. Note we should be careful about the datatype of columns, both the tables should have the same data types. For example, col1 and column1 should have the same datatype. To be more specific we can also include a WHERE clause to insert only particular records.

INSERT INTO employees (first_name, last_name, department, salary)
SELECT first_name, last_name, department, salary
FROM new_employees;

From the above code we can see that records from new_employees have been copied to the employees table. Before we had 4 records and now we have 7 records.

INSERT with Default Values

We can omit certain columns while inserting records. This can be done when there are some default values defined or are null.

INSERT INTO employees (first_name, last_name, department)
VALUES ('Eve', 'Silver', 'Operations');

In the above code we have not specified the salary value. Hence the default value NULL will be taken in place of salary.

INSERT IGNORE

INSERT IGNORE statement ignores if there is any error due to duplicate keys or other issues. You can use this IGNORE clause when you don’t want interruption during insertion.

INSERT IGNORE INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (1, 'Frank', 'Gray', 'Sales', 60000);

In the above image we can see that 0 rows are affected even when we tried to pass a record with a duplicate key. Since we have used IGNORE even when there are some issues with INSERT statement it will just ignore the insertion.

INSERT ON DUPLICATE KEY UPDATE

Using IGNORE we basically skip the failing insertion statements. Using INSERT ON DUPLICATE KEY UPDATE, when there are conflicts we can update the existing record.

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (1, 'Frank', 'Gray', 'Sales', 60000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

In the above image we can see that when the insertion failed due to the duplicate key, it just updated the salary of that record. We can see that the salary got updated from 80000 to 60000.

Conclusion

The SQL INSERT statement is a fundamental tool for adding new data to your database tables. Mastering its various forms—from basic single-row inserts to more advanced techniques like inserting multiple rows and using the INSERT INTO … SELECT statement—ensures you can efficiently manage and update your data.

Understanding and following the best practices, such as specifying columns and handling duplicates, will help you maintain data integrity and improve performance. Whether you’re migrating data, integrating new records, or performing batch inserts, the INSERT statement is an essential part of effective database management.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

badrinarayan6645541 21 Jun, 2024

Data science intern at Analytics Vidhya, specializing in ML, DL, and AI. Dedicated to sharing insights through articles on these subjects. Eager to learn and contribute to the field's advancements. Passionate about leveraging data to solve complex problems and drive innovation.