How To Insert Data in SQL

Introduction

Structured Query Language, more commonly known as SQL, provides a great deal of flexibility in terms of how it allows you to insert data into tables. For instance, you can specify individual rows of data with the VALUES keyword, copy entire sets of data from existing tables with SELECT queries, as well as define columns in ways that will cause SQL to insert data into them automatically.

In this guide, we’ll go over how to use SQL’s INSERT INTO syntax to add data to tables with each of these methods.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.

You’ll also need a database and table with which you can practice inserting data. If you don’t have these, you can read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and table which this guide will use in examples throughout.

Connecting To MySQL and Setting Up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

Create a database named insertDB:

CREATE DATABASE insertDB;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the insertDB database, run the following USE statement:

USE insertDB;

Output
Database changed

After selecting the insertDB database, create a table within it. As an example, let’s say you own a factory and want to create a table to store some information about your employees. This table will have the following five columns:

• name: each employee’s name, expressed using the varchar data type with a maximum of 30 characters
• position: this column will store each employee’s job title, again expressed using the varchar data type with a maximum of 30 characters
• department: the department in which each employee works, expressed using the varchar data type but with a maximum of only 20 characters
• hourlyWage: a column to record each employee’s hourly wage, it uses the decimal data type with any values in this column limited to a maximum of four digits in length with two of those digits to the right of the decimal point. Thus, the range of values allowed in this column goes -99.99 to 99.99
• startDate: the date each employee was hired, expressed using the date data type. Values of this type must conform to the format YYYY-MM-DD

Create a table named factoryEmployees that has these five columns:

CREATE TABLE factoryEmployees (
name varchar(30),
position varchar(30),
department varchar(20),
hourlyWage decimal(4,2),
startDate date
);

With that, you’re ready to follow the rest of the guide and begin learning about how to insert data with SQL.

Inserting Data Manually

The general syntax for inserting data in SQL looks like this:

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

To illustrate, run the following INSERT INTO statement to load the factoryEmployees table with a single row of data:

INSERT INTO factoryEmployees
(name, position, department, hourlyWage, startDate)
VALUES
('Agnes', 'thingamajig foreman', 'management', 26.50, '2017-05-01');

Output
Query OK, 1 row affected (0.00 sec)

This statement begins with the INSERT INTO keywords, followed by the name of the table in which you want to insert the data. Following the table name is a list of the columns to which the statement will add data, wrapped in parentheses. After the column list is the VALUES keyword, and then a set of values wrapped in parentheses and separated by commas.

The order in which you list the columns does not matter. It’s important to remember that the order of the values you supply aligns with the order of the columns. SQL will always try to insert the first value given into the first column listed, the second value into the next column, and so on. To illustrate, the following INSERT statement adds another row of data, but lists the columns in a different order:

INSERT INTO factoryEmployees
(department, hourlyWage, startDate, name, position)
VALUES
('production', 15.59, '2018-04-28', 'Jim', 'widget tightener');

Output
Query OK, 1 row affected (0.00 sec)

If you don’t align the values correctly, SQL may enter your data into the wrong columns. Additionally, it will cause an error if any of the values conflict with the column’s data type, as in this example:

INSERT INTO factoryEmployees
(name, hourlyWage, position, startDate, department)
VALUES
('Louise', 'doodad tester', 16.50, '2017-05-01', 'quality assurance');

Output
ERROR 1366 (HY000): Incorrect decimal value: 'doodad tester' for column 'hourlyWage' at row 1

Be aware that while you must provide a value for every column you specify, you aren’t necessarily required to specify every column in a table when adding a new row of data. As long as none of the columns you omit have a constraint that would cause an error in this case (such as NOT NULL), MySQL will add NULL to any unspecified columns:

INSERT INTO factoryEmployees
(name, position, hourlyWage)
VALUES
('Harry', 'whatzit engineer', 26.50);

Output
Query OK, 1 row affected (0.01 sec)

If you plan to enter a row with values for every column in the table, you don’t need to include the column names at all. Keep in mind that the values you enter must still align with the order the columns were defined in the table’s definition.

In this example, the values listed align with the order in which the columns were defined in the factoryEmployee table’s CREATE TABLE statement:

INSERT INTO factoryEmployees
VALUES
('Marie', 'doodad welder', 'production', 27.88, '2018-03-29');

Output
Query OK, 1 row affected (0.00 sec)

You can also add multiple records at once by separating each row with a comma, like this:

INSERT INTO factoryEmployees
VALUES
('Giles', 'gizmo inspector', 'quality assurance', 26.50, '2019-08-06'),
('Daphne', 'gizmo presser', 'production', 32.45, '2017-11-12'),
('Joan', 'whatzit analyst', 'quality assurance', 29.00, '2017-04-29');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Copying Data with SELECT Statements

Rather than specifying data row by row, you can copy multiple rows of data from one table and insert them into another with a SELECT query.

The syntax for this sort of operation looks like this:

INSERT INTO table_A (col_A1, col_A2, col_A3)
SELECT col_B1, col_B2, col_B3
FROM table_B;

Instead of following the column list with the VALUES keyword, this example syntax follows it with a SELECT statement. The SELECT statement in this example syntax only includes the FROM clause, but any valid query can work.

To illustrate, run the following CREATE TABLE operation to create a new table named showroomEmployees. Note that this table’s columns have the same names and data types as three columns from the factoryEmployees table used in the previous section:

CREATE TABLE showroomEmployees (
name varchar(30),
hourlyWage decimal(4,2),
startDate date
);

Output
Query OK, 0 rows affected (0.02 sec)

Now you can load this new table with some data from the factoryEmployees table created previously by including a SELECT query in the INSERT INTO statement.

If the SELECT query returns the same number of columns in the same order as the target table’s columns, and they also have compatible matching data types, you can omit the column list from an INSERT INTO statement:

INSERT INTO showroomEmployees
SELECT
factoryEmployees.name,
factoryEmployees.hourlyWage,
factoryEmployees.startDate
FROM factoryEmployees
WHERE name = 'Agnes';

Output
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

INSERT INTO showroomEmployees
SELECT
name,
hourlyWage,
startDate
FROM factoryEmployees
WHERE name = 'Agnes';

The SELECT statement in this operation includes a WHERE clause which causes the query to only return rows from the factoryEmployees table whose name column contains the value Agnes. Because there’s only one such row in the source table, only that row will get copied over to the showroomEmployees table.

To confirm this, run the following query to return every record in the showroomEmployees table:

SELECT * FROM showroomEmployees;

Output
+-------+------------+------------+
| name  | hourlyWage | startDate  |
+-------+------------+------------+
| Agnes |      26.50 | 2017-05-01 |
+-------+------------+------------+
1 row in set (0.00 sec)

You can insert multiple rows of data with any query that will return more than one row from the source table. For example, the query in the following statement will return every record in the factoryEmployees database in which the value in the name column does not start with J:

INSERT INTO showroomEmployees
SELECT
factoryEmployees.name,
factoryEmployees.hourlyWage,
factoryEmployees.startDate
FROM factoryEmployees
WHERE name NOT LIKE 'J%';

Output
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Run this query again to return every record in the showroomEmployees table:

SELECT * FROM showroomEmployees;

+--------+------------+------------+
| name   | hourlyWage | startDate  |
+--------+------------+------------+
| Agnes  |      26.50 | 2017-05-01 |
| Agnes  |      26.50 | 2017-05-01 |
| Harry  |      26.50 | NULL       |
| Marie  |      27.88 | 2018-03-29 |
| Giles  |      26.50 | 2019-08-06 |
| Daphne |      32.45 | 2017-11-12 |
+--------+------------+------------+
6 rows in set (0.00 sec)

Notice that there are two identical rows with Agnes in the name column. Every time you run an INSERT INTO statement that uses SELECT, SQL treats the results from the query as a new set of data. Unless you impose certain constraints on your table or develop more granular queries, there’s nothing to prevent your database from being loaded with duplicate records when adding data like this.

Inserting Data Automatically

When creating a table, you can apply certain attributes to columns that will cause the RDBMS to populate them with data automatically.

To illustrate, run the following statement to define a table named interns. This will create a table named interns that has three columns. The first column in this example, internID, holds data of the int type. Notice, though, that it also includes the AUTO_INCREMENT attribute. This attribute will cause SQL to automatically generate a unique numeric value for every new row, starting with 1 by default and then incrementing up by one with each subsequent record.

Similarly, the second column, department, includes the DEFAULT keyword. This will cause the RDBMS to insert the default value — 'production' in this example — automatically if you omit department from an INSERT INTO statement’s column list:

CREATE TABLE interns (
internID int AUTO_INCREMENT PRIMARY KEY,
department varchar(20) DEFAULT 'production',
name varchar(30)
);

To demonstrate these features, load the interns table with some data by running the following INSERT INTO statement. This operation only specifies values for the name column:

INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');

Output
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Then run this query to return every record from the table:

SELECT * FROM interns;

Output
+----------+------------+----------+
| internID | department | name     |
+----------+------------+----------+
|        1 | production | Pierre   |
|        2 | production | Sheila   |
|        3 | production | Francois |
+----------+------------+----------+
3 rows in set (0.00 sec)

This output indicates that because of the columns’ definitions, the previous INSERT INTO statement added values into both internID and department even though they weren’t specified.

To add a value other than the default to the department column you would need to specify that column in the INSERT INTO statement, like this:

INSERT INTO interns (name, department)
VALUES
('Jacques', 'management'),
('Max', 'quality assurance'),
('Edith', 'management'),
('Daniel', DEFAULT);

Output
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Notice that the last row of values provided in this example includes the DEFAULT keyword instead of a string value. This will cause the database to insert the default value ('production'):

SELECT * FROM interns;

Output
+----------+-------------------+----------+
| internID | department        | name     |
+----------+-------------------+----------+
|        1 | production        | Pierre   |
|        2 | production        | Sheila   |
|        3 | production        | Francois |
|        4 | management        | Jacques  |
|        5 | quality assurance | Max      |
|        6 | management        | Edith    |
|        7 | production        | Daniel   |
+----------+-------------------+----------+
7 rows in set (0.00 sec)

Conclusion

By reading this guide, you learned several different ways to insert data into a table, including specifying individual rows of data with the VALUES keyword, copying entire sets of data with SELECT queries, and defining columns which SQL will insert data into automatically.

The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of how it handles the INSERT INTO statement and what options are available for it.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.