Course 15: Add data: insert in SQL

Oct 2, 2021
Course 15: Add data: insert in SQL

4.1. General form of insert

In a database, data (precisely) is reduced to rows of tables. Inserting rows into a table is therefore one of the fundamental elements in the processing chain for this data.

The general syntax of a command insertis as follows.

Example 53. General form of insert

INSERT   INTO table_name
    VALUES (list_of_values)
      [, (list_of_values)]

4.2. Copy from one table to another

There is also a form that allows you to directly insert the result of a query into a table, and therefore to make a copy, total or partial.

Example 54. Direct insertion of a subquery

INSERT   INTO table_name

Let’s see how we can use this mechanism to copy part of the table Marinsinto a table Noms_marins. Let’s start by creating this table.

Now let’s write the actual copy.

Example 55. Partial copy from one table to another

create   table Sailors_names (
   name      varchar (32),
   firstname   varchar (32)
 insert   into Sailors_names
     select (name, firstname) 
     from Sailors
     where ddmort   is   null ;

In our example, we only copy the columns nomand prenomin the arrival table, and only the sailors who are still alive. Of course, this copy is made when the request is launched. Adding items to the table Marinsdoes not update the table Noms_marins.

Note first of all that, technically, the list of columns in parameter of insertis optional. Although it is, it is very dangerous not to put it, especially if all the data inserted have the same type, for example varchar.

Indeed, if the list of columns is not specified, the server will build one, in an order that is sometimes impossible to know in advance. The following values ​​will therefore be placed in the table, in columns chosen haphazardly. Behavior strongly discouraged …

Systematically specifying this list is an elementary precaution, which, moreover, allows inserting to be carried out by specifying only part of the columns of a table, and by letting the server insert the default values, or nullon the other columns.

During an insertion, the integrity constraints apply. It is therefore possible that an insertion is refused by the server.

Leave a Reply

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