Adding data, INSERT operator

The operator INSERT is used to add new records to the table.

General query structure with the INSERT operator

INSERT INTO table_name [(table_field, ...)]
VALUES (value_of_table_field, ...)
| SELECT table_field, ... FROM table_name ...

In the described request structure, optional parameters are specified in square brackets. The vertical bar denotes an alternative syntax.

Values can be inserted by enumeration using the word values listing them in parentheses separated by commas or using the operator select . Thus, You can add new entries in the following ways:

INSERT INTO Goods (good_id, good_name, type)
VALUES (5, 'Table', 2);
INSERT INTO Goods VALUES (5, 'Table', 2);
INSERT INTO Goods 
SELECT good_id, good_name, type FROM Goods where good_name = 2;

Primary key when adding a new record

It should be remembered that the primary key of the table is a unique value and adding an existing value will result in an error.

When adding a new record with unique indices, the choice of such a unique values ​​can be a daunting task. The solution may be an additional request, aimed at identifying the maximum value of the primary key to generate a new unique value.

INSERT INTO Goods SELECT COUNT(*) + 1, 'Table', 2 FROM Goods;

MySQL

MySQL introduced a mechanism for its automatic generation. To do this, just provide the primary key good_id attribute AUTO_INCREMENT. Then when creating a new record as the value good_id just pass NULL or 0 - the field will automatically receive a value, equal to the maximum value of the good_id column, plus one.

CREATE TABLE Goods (
	good_id INT NOT NULL AUTO_INCREMENT
	...
 );
INSERT INTO Goods VALUES (NULL, 'Table', 2);

PostgreSQL

PostgreSQL has a similar mechanism for automatically generating a unique identifier. For this, it has types SMALLSERIAL SERIAL BIGSERIAL, which are not real types, but rather just the convenience of writing columns with a unique identifier. A column with one of the above types will be integer and will automatically grow when a new record is added.

CREATE TABLE Goods (
	good_id SERIAL
	...
 );
INSERT INTO Goods (good_name, type) VALUES ('Table', 2);
Query union, UNION operator
Syntax of UPDATE operator