Blog

In this article on SQL language I am going to explain how record inserts are performed in databases.

I will use SQL queries only to show you the ways to insert data in this language, how queries are executed correctly and what you should take into account to avoid errors.

So that you understand every last paragraph and so that you can address the point that most interests you, I have divided this article into the following sections:

1. How to insert information into tables of databases with SQL.
2. Complete form of use of the INSERT statement.
3. Short form of the INSERT statement.
4. advanced INSERT. Insert information from a SELECT query.
   1. With queries filtered with WHERE.
   2. With use of SELECT and subqueries.

Content

• Insert records into database with SQL
• INSERT the SQL statement to insert data
  • Complete way to use the INSERT statement
  • Reduced INSERT query without columns
• Insert information from a SELECT query
  • Insert with SELECT and filter with WHERE
  • Insert with subqueries in the WHERE

Insert records into database with SQL

To insert data into the database we must always do it through the tables, that is, insert a row into a table determined.

The data to be inserted in a table must respect all the restrictions that exist on it, that is, on its columns. Therefore, the types of the columns, fields that cannot be left empty or any type of restriction (CONSTRAINT) on the columns must be taken into account.

INSERT the SQL statement to insert data

The insertion of data in the tables of your database will be always through the SQL INSERT statement. The use of this sentence is simple, we will compose the query indicating in which table we are going to insert a row, in which columns and which data:

There are two ways to use the INSERT query:

1. Specifying the columns and their values ​​respectively.
2. Without indicating the columns.

Complete way to use the INSERT statement

This is the way I always use and the one I recommend you always use. Because? It is the most complete, the most versatile and the one that avoids the greatest number of errors.

The scheme of use of this SQL INSERT would be like this:

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

And now here is a real example of use:

INSERT INTO videojuego ( nombre, productora, edad_recomendada ) VALUES ( ‘zelda: breath of the wild’, ‘Nintendo’, 15);

Notice how I have indicated each column in which I am going to insert data in a certain order: first the “order” column, secondly the “producer” column and thirdly “recommended_age”.

The order of the values ​​that come after the VALUES statement follows the order of the columns, so the database system will know exactly which column to insert each piece of data into.

Each INSERT query, as you’ve seen it, just insert a record ( row ).

Reduced INSERT query without columns

This query is a shortened version of the standard INSERT statement. You could use this version, mainly, when you are going to insert a complete record, with a value for each column of the table in question or.

The schema of this INSERT query is as follows:

INSERT INTO table_name VALUES ( value1, value2, …, valueN)

Remember that the values ​​entered correspond to the order of the columns in the table.

Below I show you an example. The following INSERT query:

INSERT INTO videojuego VALUES ( ‘zelda: breath of the wild’, ‘Nintendo’, 15);

I have launched this INSERT query for a table videogame created with the following CREATE query:

CREATE TABLE videojuego (
     nombre VARCHAR(70),
     productora VARCHAR(50),
     edad_recomendada INT
);

Observe the correspondence between the values ​​entered in the INSERT, their types and order, and in the order of creation of the columns of the CREATE query (from top to bottom).

Insert information from a SELECT query

What has been seen in the previous points is the way to insert data from record to record, but now I want to show you a different point of view to insert records into one table extracted from another.

We are going to combine two types of queries in SQL: a selection with SELECT and an insertion with INSERT. We will use a normal SELECT query to be able to extract the data from another table and later insert it into the INSERT.

The schema of use of this query would be:

INSERT INTO table(column1, column2, …, columnN) SELECT column1, column2, …, columnN FROM table2

For example you could use this query to insert data from a table videogame_historical to the table videogame seen in the previous examples:

INSERT INTO videojuego (nombre, productora, edad_recomendada)
SELECT nombre, productora, edad_recomendada
FROM videojueo_historico;

like this table videogame_historical contains compatible data, I have selected all its information with the SELECT query and inserted it into the columns indicated in the INSERT.

Note that, as in the rest of the article, I respect the order of the columns that I indicate between parentheses and in the SELECT.

Insert with SELECT and filter with WHERE

The SELECT query used to select the records to insert can be any type of query you know of or have done, as long as you respect the column mapping.

For example, you could repeat the previous query but this time only for those selecting 18+ video games:

INSERT INTO videojuego (nombre, productora, edad_recomendada)
SELECT nombre, productora, edad_recomendada
FROM videojueo_historico
WHERE edad_recomendada >= 18;

Insert with subqueries in the WHERE

This time I’m going to go further, so that you definitely get an idea of ​​everything that can be done, and I’m going to perform the INSERT query with the data from a data select with subquery.

Specifically, I am going to insert all the records from the videogame_historico table that were not already in the videogame table:

INSERT INTO videojuego (nombre, productora, edad_recomendada)
SELECT nombre, productora, edad_recomendada
FROM videojuego_historico
WHERE NOT EXISTS ( SELECT * 
			     FROM videojuego
			     WHERE videojuego.nombre = videojuego_historico.nombre );