SQL-Lesson 13. Adding data (INSERT INTO)

In the previous sections, we considered the work of retrieving data from previously created tables. Now it's time to figure out how we can create/delete tables, add new records and delete old ones.

For these purposes, there are such operators in SQL as:

CREATE - creates a table;

ALTER - changes the structure of the table;

DROP - deletes a table or field;

INSERT - adds data to the table.

Let's start getting acquainted with this group of operators with the INSERT operator.

1. Adding whole lines

As the name suggests, the INSERT INTO operator is used to insert (add) rows to a database table.

Adding can be done in several ways:

add one full line;

add part of a new line;

add query results.

So, to add a new row to the table, we need to specify the table name, list the column names, and specify the values for each column using the INSERT INTO table_name(field1, field2 ...) VALUES (value1, value2 ...) construct. Let's consider an example.

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) 
VALUES ('6', '1st Street', 'Los Angeles', 'Harry Monroe', 'USA')

You can also change the order of column names, but at the same time you need to change the order of values in the VALUES parameter.

2. Adding part of the lines

In the previous example, when using the INSERT operator, we explicitly specified the names of the table columns. Using this syntax, we can skip some columns. This means that you enter values for some columns and do not provide them for others. Example:

INSERT INTO Sellers (ID, City, Seller_name) 
VALUES ('6', 'Los Angeles', 'Harry Monroe')

In this example, we have not specified values for the two columns Address and Country. You can exclude some columns from the aaa statement if it allows you to define the table. In this case, one of the following conditions must be met: this column is defined as allowing a null value (absence of any value) or a default value is specified in the table definition. This means that if no value is specified, the default value will be used. If you omit a column from a table that does not allow NULL values to appear in its rows and does not have a value defined for default use, the DBMS will issue an error message and the row will not be added.

3. Adding selected data

In the previous examples, we inserted data into the table by typing them manually in the query. However, the INSERT INTO operator allows us to automate this process if we want to insert data from another table. For this, there is such a construction in SQL as INSERT INTO ... SELECT ... . This design allows you to simultaneously select data from one table and insert them into another. Suppose we have another table Sellers_EU with a list of sellers of our product in Europe and we need to add them to the general table Sellers. The structure of these tables is the same (the same number of columns and the same names), but the data is different. To do this, we can write the following query:

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) 
             SELECT ID, Address, City, Seller_name, Country 
             FROM Sellers_EU

It is necessary to pay attention so that the values of the internal keys are not repeated (ID field), otherwise an error will occur. The SELECT statement can also include a WHERE clause to filter data. It should also be noted that the DBMS does not pay attention to the names of the columns contained in the SELECT operator, it only cares about the order of their location. Therefore, the data in the first specified column that was selected through SELECT will in any case be filled in the first column of the Sellers table specified after the INSERT INTO operator, regardless of the field name.

4. Copying data from one table to another

Often, when working with databases, there is a need to create copies of any tables, for the purpose of backup or modification. To make a complete copy of the table in SQL, a separate operator SELECT INTO is provided. For example, we need to create a copy of the table Sellers, we will need to write the query as follows:

SELECT * INTO Sellers_new 
FROM Sellers

Unlike the previous construction INSERT INTO ... SELECT ... when data is added to an existing table, the construction SELECT ... INTO ... FROM ... copies data to a new table. It can also be said that the first construction imports data, and the second one exports it.

When using the construction SELECT ... INTO ... FROM ... the following should be taken into account:

you can use any clauses in the SELECT statement, such as GROUP BY and HAVING;

you can use a union to add data from several tables;

data can be added to only one table, regardless of how many tables it was taken from.