Enter records (insert into-select)

A record is a row in the table that contains the data itself. Each record has one data for each column (field). Our "users" table consists of 2 fields, "name" and "password".

When entering the data for each record, the quantity and order of the fields must be taken into account.

The basic and general syntax is as follows:

 insert into TABLENAME (FIELDNAME1, ..., FIELDNAMEn)values ​​(FIELD VALUE1, ..., FIELD VALUEn);

We use "insert into", then the name of the table, we detail the names of the fields between parentheses and separated by commas and after the "values" clause we place the values ​​for each field, also between parentheses and separated by commas.

In the following example a record is added to the "users" table, in the "name" field "Mariano" will be stored and "clown" will be stored in the "password" field:

 insert into users (name, password)  values ​​('Mariano', 'clown');

After each insertion, a message appears indicating the number of records entered.

Note that the data entered, as it corresponds to character strings, is enclosed in single quotes.

To see the records of a table we use "select":

select * from users;

The "select" command retrieves the records from a table. With the asterisk we indicate that it shows all the fields of the "users" table.

The table appears, its fields and records entered; if you don't have records, only the fields and the empty table would appear).

It is important to enter the values ​​in the same order in which the fields are named: In the following example the "key" field is listed first and then the "name" field, therefore, the values ​​are also placed in that order:

 insert into users (password, name)

  values ​​('River', 'Juan');

If we enter the data in an order other than the order in which the fields were named, an error message does not appear and the data is saved incorrectly.

The following example places the values ​​in a different order in which the fields are named, the value of the key (the string "Boca") will be stored in the field "name" and the value of the name (the string "Luis") in the "key" field:

 insert into users (name, password) values ​​('Boca', 'Luis');

Insert Values Directly Without Column Names

If we enter values ​​for all fields, we can omit the list of field names.

For example, if we have created the table "books" with the fields "title", "author" and "publisher", we can enter a record in the following way:

 insert into books values ​​('One', 'Richard Bach', 'Planet');

It is also possible to enter values ​​for some fields. We enter values ​​only for the "title" and "author" fields:

 insert into books (title, author)

  values ​​('El aleph', 'Borges');

Oracle will store the value "null" in the "editorial" field, for which we have not specified a value.

When entering records we must take into account:

• the list of fields must match in quantity and type of values ​​with the list of values ​​after "values". If more (or fewer) fields are listed than the entered values, an error message appears and the statement is not executed.
• if we enter values ​​for all fields we can skip the list of fields.
• we can omit values ​​for the fields that allow null values ​​("null" will be saved); if we omit the value for a "not null" field, the statement is not executed.