How to insert data into SQL

  • 465 views
  • 9 minute read
Hadi Bahadori
  • July 20, 2024
0 Shares
0
0
0
0
0
Shares
0
0
0
0

Introduction

Structured Query Language, better known as SQL, provides a lot of flexibility in how you insert data into tables. For example, you can specify individual rows of data with the VALUES keyword, copy entire sets of data from existing tables with SELECT queries, and define columns in a way that causes SQL to automatically insert data into them.

In this guide, we will explore how to use the INSERT INTO SQL syntax to add data to tables with each of these methods.

Prerequisites

To follow this guide, you will need a computer that has some kind of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide have been verified using the following environment:

  • A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW.
  • MySQL is installed and secured on the server.

You'll also need a database and table that you can practice inserting data into. If you don't have these, you can read the Connecting to MySQL and Setting Up a Sample Database section for more details on how to create the database and table that this guide uses in the examples.

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 the MySQL server prompt and replace Sami with your MySQL username:

mysql -u sammy -p

Create a database called insertDB:

CREATE DATABASE insertDB;

If the database was created successfully, you will receive the following 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. For example, suppose you own a factory and you want to create a table to store some information about your employees. This table will have the following five columns:

  • name: The name of each employee expressed using the varchar data type with a maximum of 30 characters
  • position: This column stores the job title of each employee, which is 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 the hourly wage of each employee uses the decimal data type with the values in this column being up to four digits long with two of those digits to the right of the decimal point. Therefore, the range of allowed values in this column is -99.99 to 99.99.
  • startDate: The date of hire of each employee, expressed using the Date data type. Values of this type must conform to the format YYYY-MM-DD

Create a table called factoryEmployees with these five columns:

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

With that, you are ready to follow the rest of the guide and start learning how to insert data with SQL.

Enter data manually

The general syntax for inserting data in SQL is as follows:

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

To demonstrate, run the following INSERT INTO statement to load the factoryEmployees table with one 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)

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

The order in which you list the columns does not matter. It is important to remember that the order of the values you provide must match the order of the columns. SQL always tries to insert the first value given in the first column listed, the second value in 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 insert your data into the wrong columns. Additionally, it will throw an error if any of the values conflict with the column's data type, like 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

Note that while you must provide a value for each column you specify, you don't necessarily have to specify every column in the table when adding a new row of data. As long as none of the columns you're dropping have a constraint that would cause an error in this case (such as NOT NULL), MySQL will add NULL to 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 are going to insert a row with values for each column in the table, you don't need to enter the column names at all. Keep in mind that the values you enter must still be consistent with the order in which the columns are defined in the table definition.

In this example, the values listed are aligned in the order in which the columns are defined in the CREATE TABLE statement for the factoryEmployee table:

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

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

The syntax for this type of operation is as follows:

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 will work.

To demonstrate, run the following CREATE TABLE operation to create a new table called showroomEmployees. Note that the names and data types of the columns in this table are the same as the 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)

You can now load this new table with some data from the factoryEmployees table that was previously created 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 columns in the target table, and they also have matching data types, you can omit the column list from the 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

The SELECT statement in this operation includes a WHERE clause that causes the query to return only rows from the factoryEmployees table whose name column contains the value Agnes. Since there is only one row in the source table, only that row is copied to the showroomEmployees table.

To verify this, run the following query to return every record in the showroom employees 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 returns more than one row from the source table. For example, the query in the following statement returns any record in the factoryEmployees database where the value in the name column does not begin 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

To return every record in the showroom employees table, run this query again:

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. Each time you execute an INSERT INTO statement that uses SELECT, SQL treats the results of the query as a new set of data. Unless you impose specific constraints on your table or create more precise queries, there is nothing to prevent your database from being loaded with duplicate records when you add data like this.

Automatically insert information

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

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

Similarly, the second column, section, contains the keyword DEFAULT. If you remove section from the column list of the INSERT INTO statement, it will cause the RDBMS to automatically insert the default value – “production” in this example:

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 specifies only the 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 shows that due to the column definitions, the previous INSERT INTO statement adds values to both the inner and outer sections, even though they are not specified.

To add a value other than the default value to a section column, you must 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

Note that the last row of values provided in this example contains the keyword DEFAULT instead of a string value. This causes 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)

Result

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 data sets with SELECT queries, and defining columns that SQL will automatically insert data into.

The commands listed here should work on any database management system that uses SQL. Keep in mind that each SQL database uses its own unique implementation of the language, so you should refer to your DBMS's documentation for a more complete explanation of how the INSERT INTO statement is handled and the options available for it.

In this article:
,
Post written by:
Follow
Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Article
Next Article
You May Also Like