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 INTO table_name [(column_list)] VALUES (list_of_values) [, (list_of_values)]
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.
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
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
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
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.