How to Insert Data into SQL Databases

The data insertion operation in relational databases is one of the most essential tasks. In this article, we will explain how to perform this operation using relational database management systems (DBMS) that work with the SQL language. We will use MySQL, the most popular SQL-based DBMS.

As an example, we will consider two tables: one for leading European football clubs (including their national affiliation, year of establishment, number of national championships won, domestic cups, and European trophies), and another for some leading countries worldwide (with their capitals and population in millions). Initially, the tables (let's call them Clubs and Countries) will be empty, containing only column names. Our task is to populate them using various SQL commands so that they appear as follows:

The Countries table:

So, first, we need to create a database and two tables. 

Connect to the MySQL server (replace xxx.xxx.xxx.xxx with the appropriate IP address):

mysql -u root -h xxx.xxx.xxx.xxx -p

Then enter the following command:

CREATE DATABASE TestDB;

To verify that the new database has been successfully created, use the command:

SHOW DATABASES;

Our database, TestDB, should appear in the list. Next, we need to grant users access to this database. For example, if we already have a user test_user, we can grant them access using the command:

GRANT ALL PRIVILEGES ON TestDB.* TO 'test_user'@'%' WITH GRANT OPTION;

Now, we can proceed to create the tables. Let’s start with the table for clubs:

CREATE TABLE Clubs 
(
    Club VARCHAR(64) NOT NULL,
    Country VARCHAR(32),
    Year INT,
    Champs INT,
    Cups INT,
    Eurocups INT
);

This means that for the first two columns, we specified string values, with the length of the data in each cell not exceeding 64 and 32 characters, respectively. Additionally, cells in the Club column cannot be empty when adding data (NOT NULL). For the remaining four columns, we designated integer values (INT).

Now, following the same pattern, we create the second table:

CREATE TABLE Countries 
(
    Country VARCHAR(32) NOT NULL,
    Capital VARCHAR(32),
    Population INT
);

That’s it! Our tables are created and ready to be populated.

The INSERT INTO Statement

Copy link

The INSERT INTO statement allows you to insert data into an SQL table. However, the data is inserted in the column order, so you must know the exact sequence of columns in the table. Let’s insert data into the first rows of our tables:

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);
INSERT INTO Countries VALUES("Russia", "Moscow", 147);

Note that we must specify values for all columns. For instance, the following entries would result in errors or misaligned values (e.g., we omitted the club’s founding year and the country’s capital):

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 35, 19, 21);
INSERT INTO Countries VALUES("Russia", 147);

The INSERT INTO Statement with Column List

Copy link

This method is more reliable as it prevents errors when skipping columns. However, you must specify the column names explicitly:

INSERT INTO Clubs(Club, Country, Champs, Cups, Eurocups) VALUES("Barcelona", "Spain", 26, 31, 18);
INSERT INTO Countries(Country, Capital) VALUES("USA", "Washington");

In these examples, we skipped the club's founding year (the Year column) in the first case and the population (the Population column) in the second. No errors occur because these fields simply remain empty and can be filled later.

The INSERT INTO Statement for Bulk Insertion

Copy link

Adding data row by row is not always convenient. Therefore, let’s look at how to insert data into multiple rows simultaneously. We can do it with the following command:

INSERT INTO Clubs(Club, Country, Eurocups)
VALUES
("Real Madrid", "Spain", 21),
("Barcelona", "Spain", 18),
("Milan", "Italy", 14),
("Juventus", "Italy", 8),
("Bavaria", "Germany", 10);
INSERT INTO Countries(Country, Population)
VALUES
("Russia", 147),
("USA", 336),
("China", 1427),
("India", 1435),
("Brazil", 218);

In the first table, we populated the columns with the names of the clubs, their national affiliation, and the number of European trophies won, leaving the remaining columns empty. In the second table, we omitted the countries' capitals.  As we can see, there are some syntax differences, and the commands are split across multiple lines.

The SET Statement

Copy link

In combination with INSERT INTO, the SET statement allows you to insert a single record into a table:

INSERT INTO Clubs SET
Club="Milan",
Country="Italy",
Year=1899,
Champs=19,
Cups=5,
Eurocups=14;
INSERT INTO Countries SET
Country="China",
Capital="Beijing",
Population=1427;

However, this is also its drawback, as it does not allow us to insert multiple records into an SQL table at once. For that purpose, the previous method is more suitable.

How to Insert Data from Another SQL Table

Copy link

Suppose we have other tables with the same columns, and we want to add their data to our tables. This can be done using the SELECT statement in combination with the familiar INSERT:

INSERT INTO Clubs(Club, Country, Year, Champs, Cups, Eurocups)
SELECT Club, Country, Year, Champs, Cups, Eurocups
FROM Clubs2;
INSERT INTO Countries(Country, Capital, Population)
SELECT Country, Capital, Population
FROM Countries2;

The IGNORE Clause for Avoiding Errors

Copy link

We can use the IGNORE clause to prevent MySQL from halting when trying to insert invalid values. For example, if we set a uniqueness constraint on the Club column to ensure that each club name is unique, the clause helps avoid interruptions. In MySQL, the first value in a table is always treated as unique, so manually adding such a constraint may not be necessary.

For instance, if we already have a row like this:

This command will result in an error:

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

We already have a row with the value Real Madrid in the first column. However, duplicates are likely to occur when copying data from multiple tables. To ensure the program ignores these duplicates without generating errors, we use the IGNORE clause:

INSERT IGNORE Clubs(Club, Country, Year, Champs, Cups, Eurocups)
VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

The same is true for the second example. We already have the line: 

Running this query:

INSERT INTO Countries VALUES("Russia", "Moscow", 147);

— will result in an error. That’s why we should use IGNORE:

INSERT IGNORE Countries(Country, Capital, Population)
VALUES("Russia", "Moscow", 147);

The program will simply ignore the duplicate row and continue execution without throwing an error.

The LOAD DATA Statement for Importing from a Text File

Copy link

Suppose we have an empty Clubs table with the appropriate columns, and we need to populate it with information from a text file. The LOAD DATA statement allows us to do this. However, you first need to prepare the text file with the data. Open your file (let's say Clubs.txt) in a text editor and format the data as follows, separating values with a tab character:

'Real Madrid'    'Spain'    '1902'    '35'    '19'    '21'
'Barcelona'    'Spain'    '1899'    '26'    '31'    '18' 
'Milan'    'Italy'    '1899'    '19'    '5'    '14'
'Juventus'    'Italy'    '1897'    '36'    '14'    '8'
'Bavaria'    'Germany'    '1900'    '32'    '20'    '10'

This SQL query will insert data in the table, placing them in the right columns. But what if we don’t have values for specific columns? Suppose we don’t know when the clubs were established. The record will look like this:

'Real Madrid'    'Spain'    '\N'    '35'    '19'    '21'
'Barcelona'    'Spain'    '\N'    '26'    '31'    '18' 
'Milan'    'Italy'    '\N'    '19'    '5'    '14'
'Juventus'    'Italy'    '\N'    '36'    '14'    '8'
'Bavaria'    'Germany'    '\N'    '32'    '20'    '10'

\N means that the cell in the table will remain empty. Now, we just need to load the data into SQL, but first, let's enable working with local files like this:

set global local_infile=true;
exit
mysql --local_infile=1 -u test_user -h xxx.xxx.xxx.xxx -p

The Linux command to load data is:

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt' INTO TABLE Clubs;

And in Windows, it is done like this:

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt'' INTO TABLE Clubs LINES TERMINATED BY '\r\n';

However, sometimes the system might not respond to these instructions. In that case, you will need to enable working with local files in MySQL, which is explained in detail in the official documentation. To summarize, the value of unsigned int in the MYSQL_OPT_LOCAL_INFILE option, related to the mysql_options() settings, must be non-zero.

That’s all for now! Now you know how to insert data into SQL databases using various statements. Just choose the most appropriate statement for the specific case, and you’ll avoid issues when copying data.