Skillversitas

INSERT Query

The INSERT Query is used to add new data to a table. After creating the table structure, the next step is to fill the table with the required data.

Basic INSERT Syntax

The basic INSERT INTO syntax is as follows:

INSERT INTO table_name (column1, column2, column3)
VALUES (grades1, grades2, grades3);

We specify the column names to be filled, then provide their values in the corresponding order.

Example: INSERT into the EMPLOYEE Table

Let's insert our first employee record:

INSERT INTO EMPLOYEE (id, name, email, position, salary, hire_date, is_active)
VALUES (1, 'Aaron Brooks', '[email protected]', 'Software Engineer', 12000000, '2023-01-15', TRUE);

INSERT with Specific Columns

We do not need to fill in every column. Columns that are not mentioned will use their default value or NULL if allowed:

INSERT INTO EMPLOYEE (id, name, position)
VALUES (2, 'John Smith', 'Project Manager');

In this example, the email, salary, hire_date, and is_active columns are not filled. Default values will be used if available, or NULL if the column allows NULL.

INSERT Multiple Rows

MySQL allows us to insert multiple rows of data in a single INSERT statement. This is far more efficient than running INSERT one row at a time.

INSERT INTO EMPLOYEE (id, name, email, position, salary, hire_date, is_active)
VALUES
    (3, 'Anna Miller', '[email protected]', 'UX Designer', 10000000, '2023-03-01', TRUE),
    (4, 'Diana Green', '[email protected]', 'Data Analyst', 11000000, '2023-06-15', TRUE),
    (5, 'Eric Foster', '[email protected]', 'DevOps Engineer', 13000000, '2022-11-01', TRUE),
    (6, 'Fiona Hall', '[email protected]', 'QA Engineer', 9500000, '2024-01-10', TRUE),
    (7, 'Gary Hayes', '[email protected]', 'System Administrator', 11500000, '2022-08-20', FALSE);

INSERT with AUTO_INCREMENT

If a table has a column with AUTO_INCREMENT, we can omit the id column and let the database assign the value automatically:

INSERT INTO EMPLOYEE (name, email, position, salary, hire_date)
VALUES ('Hannah Reed', '[email protected]', 'Frontend Developer', 10500000, '2024-02-01');

The database will automatically fill in the next id value and use the default value for is_active.

INSERT INTO SELECT

We can copy data from one table to another using INSERT INTO ... SELECT. This is very useful for creating backups or replicating data.

-- Create a backup table
CREATE TABLE EMPLOYEE_BACKUP LIKE EMPLOYEE;

-- Copy only the active data
INSERT INTO EMPLOYEE_BACKUP
SELECT * FROM EMPLOYEE WHERE is_active = TRUE;

The query above only copies active employee data to the backup table.

INSERT Tips

  • Always specify column names explicitly so the code is easier to read and maintain
  • Use multiple-row INSERT for efficiency when inserting a large amount of data
  • Be careful with the order of values — make sure it matches the order of the listed columns
  • Use INSERT INTO SELECT to copy data between tables
  • Watch out for existing constraints — data that violates a constraint will be rejected

Complete Example

Here is an example of fully populating the EMPLOYEE table:

-- Empty the table first
DELETE FROM EMPLOYEE;

-- Insert the initial data
INSERT INTO EMPLOYEE (id, name, email, position, salary, hire_date, is_active) VALUES
(1, 'Aaron Brooks', '[email protected]', 'Software Engineer', 12000000, '2023-01-15', TRUE),
(2, 'John Smith', '[email protected]', 'Project Manager', 15000000, '2022-05-01', TRUE),
(3, 'Anna Miller', '[email protected]', 'UX Designer', 10000000, '2023-03-01', TRUE),
(4, 'Diana Green', '[email protected]', 'Data Analyst', 11000000, '2023-06-15', FALSE),
(5, 'Eric Foster', '[email protected]', 'DevOps Engineer', 13000000, '2022-11-01', TRUE);

Summary

The INSERT Query is the foundation of data management in a database. By understanding the various ways to insert data, we can efficiently populate tables with the data we need. In the next chapter, we will learn how to retrieve and display data using the SELECT Query.