INSERT SQL – adds rows to the table

The INSERT SQL statement is used to insert one or more records into a table. There are two syntaxes for the INSERT statement depending on whether you are inserting one record or more records.

Syntax of Sql INSERT command

Keywords and INSERT command parameters

• schema – authorization identifier, usually matching the name of some user
• table view – the name of the table to which rows are to be inserted; if a view is specified, the rows are inserted into the main table of the view
• subquery_1 – a subquery that the server handles in the same way as a view
• column – a column for a table or view in which a value from a VALUES phrase or subquery is entered for each inserted row; if one of the table columns is omitted from that list, the default value for the inserted row is the column value defined when creating the table. If the column list is completely omitted, the VALUES proposal or query must specify the values for all columns in the table.
• VALUES – defines the row of values to be inserted in the table or view; the value must be defined in the VALUES sentence for each column in the column list.
• subquery_2 – a subquery that returns rows to be inserted in a table; the selective list of this subquery must have the same number of columns as the INSERT approval column list
  INSERT statement with VALUES adds a single row to the table. This row contains the values defined by the VALUES phrase.

An INSERT statement with a subquery adds to the table all the rows returned by the subquery instead of VALUES. The server processes the subquery and inserts each returned row into the table. If the subquery does not select any rows, the server will not insert any rows into the table.

The subquery can access any table or view, including the target INSERT claim table. The server assigns field values in new rows based on the internal column position in the table and the order of values in the VALUES phrase or in the query selection list. If any columns are skipped in the column list, the server assigns them the default values defined when creating the table. If any of these columns have a NOT NULL restriction, the server returns an error indicating that the restriction was violated and override the INSERT statement.
When an INSERT statement is issued, any INSERT trigger defined on the table is enabled.

SQL syntax of the INSERT operator when inserting a single record into a table

INSERT INTO table
(column1, column2, … )
VALUES
(expression1, expression2, … );

INSERT SQL statement syntax when inserting multiple records into a table

INSERT INTO table
(column1, column2, … )
SELECT expression1, expression2, …
FROM source_tables
[WHERE conditions];

Parameters or arguments

• table – A table to paste the records into.
• column1, column2 – These are columns in the table for inserting values.
• expression1,2 – These values are assigned to the columns in the table. Therefore column1 will be assigned the value1, column2 will be assigned the value2, etc. ,
• source_tables – Used when inserting records from another table. This is the source table when the insertion is performed.
• WHERE conditions – It’s optional. Used when inserting records from another table. These are the conditions that must be met to insert the records.

Note:
When inserting records into a table using the INSERT SQL operator, you must specify the value for each column NOT NULL. You can omit a column from the INSERT operator only if the column allows the value to be NULL.

Example – using the INSERT operator to insert a single record:

The easiest way to use the INSERT operator is to insert one entry into a table using the VALUES keyword. Let’s look at an example of how to do this in SQL.

In this example, we have a table with the following data:

Let’s put a new entry in category. Enter the following SQL statement:

INSERT INTO categories
(category_id, category_name)
VALUES
(150, 'Miscellaneous');

One record will be inserted. Select the data again from the table of categories:

SELECT *
     FROM categories;

Here are the results you should get:

This example will insert one entry into the category table. This new entry will have category_id 150 and category_name “Miscellaneous”.

In this example, since you provide values for all columns in the categories table, you can omit the column names and instead write an INSERT operator such as this:

INSERT INTO categories
VALUES
(150, 'Miscellaneous');

However, it is dangerous for two reasons. First, the INSERT operator will generate an error if additional columns are added to the category table. Second, data will be inserted into the wrong columns if the order of the columns in the table changes. Therefore, as a rule, it is better to list the column names in the INSERT operator.

An example is using the INSERT operator to insert multiple records:

By placing the SELECT operator in the INSERT operator, you can quickly perform several insertion operations. Let’s look at an example of how to do this.

In this example, we have a table with the following data:

And the table with the following data:

Now let’s paste some information from the employees table into the customers table:

INSERT INTO customers
  (customer_id, last_name, first_name)
SELECT employee_number AS customer_id,
     last_name,
     first_name
   FROM employees
  WHERE employee_number < 1003;

NOTE: With this INSERT type, some databases require you to have column name aliases in SELECT to match the column names of the table you are inserting. As you can see in the above example, we have linked the first column in the SELECT statement to customer_id.

There will be 2 entries inserted. Select the data from the customer_id table again:

SELECT *
   FROM customers;

Here are the results you should get:

In this example, the last 2 records in the customer table were inserted using data from the employee table.

INSERT INTO

INSERT INTO Example 1

INSERT INTO dept VALUES (50, "PRODUCTION", "SAN FRANCISCO");
INSERT INTO Customers (city, cname, cnum) VALUES ('London', 'Hoffman', 2001);

INSERT INTO Example 2
The following team copies the data of the firm’s employees whose commission exceeds 25% of their income to the bonus table:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal;

INSERT INTO Example 3
If you want to insert a NULL value, you must specify it as a normal value as follows:

INSERT INTO Salespeople VALUES (1001, 'Peel',NULL,12);

INSERT INTO Example 4
You can apply the INSERT command to extract values from one table and place them in another by using the query. To do so, simply replace the VALUES sentence with the corresponding query:

INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = 'London';

MySQL INSERT

The INSERT command is used to insert new lines into the MySQL database, examples of the INSERT command are given below:

INSERT INTO Example 1.
Inserting a new row in a table_name.

INSERT INTO table_name VALUES ("1", "165", "0", "name");

INSERT INTO Example 2.
Inserting a new row in the table_name table with the data in the columns we need.

INSERT INTO table_name VALUES ('1′,'165′,'0′,'name');

In a MySQL database, you can insert many new rows using a single INSERT command.

INSERT INTO Example 3.
Inserting multiple rows in a table_name.

INSERT INTO table_name (tbl_id, chislo, chislotwo, name) VALUES ('1', '159', '34', 'name1'), ('2', '14', '61', 'name2'), ('3', '356', '8', 'name3');

SQL Tutorial: Inserting Data Into Tables