Course Module 4. Working with databases - Lecture: Inserting data into a table

INSERT INTO VALUES() statement

We figured out how to create tables, let's now take a detailed look at how to add data to a table using a SQL query.

In fact, it is very simple to do this, but there are several nuances. The simplest option for inserting data into a table requires specifying two things:

column names
column values (data)

Column names must be specified, as very often columns have default values that are not specified when inserting data. And usually every table has at least one such column, for example row id.

The general form of the query for inserting data into a table looks like this:

INSERT INTO table (column1, column2, column3) VALUES
    (value1, value2, value3),
    (value1, value2, value3),
    (value1, value2, value3);

For example, if you want to insert a new record into the user table, this is how the query would look like:

INSERT INTO user (name, level, created_time) VALUES
	('Rabinovich', 5, ‘2022-06-06’);

INSERT INTO SELECT statement

Another common scenario for inserting data into a table is to take it from another table, schema, or even a DBMS.

To do this, there is another INSERT INTO query format, in which, instead of the VALUES part, you can specify a query to select data.

The general form of such a request is:

INSERT INTO table (column1, column2, column3)
SELECT-request;

Let's write a query that will add all users from the employee table to the user table:

INSERT INTO user (name, created_time)
   SELECT employee.name, employee.join_date
   FROM employee;

We have various data in the employee table, but from them we select only two fields - the name and time of arrival at the company.

Also, the user table requires you to specify the user level - level. In the employee table, employees do not have a level, so we will use the fact that the user table has a default value for the level field. We just don't specify the level and SQL will set the default value.

Suppose we are not satisfied with the default value, and we want the level to be 99, and replace user.created_time with today's date, then we can write this:

INSERT INTO user (name, level, created_time)
   SELECT employee.name, 99, CURDATE()
   FROM employee;

There is a lot more that could be done, but I think this will suffice for now. You can read more on the official MySQL page.