Insert into sql oracle syntax. SQL query INSERT INTO - fill the database with information

sql INSERT INTO query makes sense when database table is created. That is, the table exists, has a name, created rows and columns. the table is created by the operator:, the table is modified by the operator.

sql INSERT INTO query - query syntax

sql INSERT INTO query has the following syntax:

INSERT INTO table_name (in brackets, if necessary, insert a list of columns where you want to insert data) VALUES inserted data1, inserted data2, inserted data3.

An IGNORE option can be inserted between INSERT and INTRO. It is optional. Used to protect primary keys when editing a table. Otherwise, if during editing there is a duplication of primary keys, then when the IGNORE option is inserted, the first row with the primary key will remain in the modified table. Other primary keys are deleted. By default, we omit this option.

There are the optional LOW_PRIORITY and DELAYED options. They determine the priorities for adding information to the database. The first one specifies waiting for the database release, the second one means information buffering.

Line in query: INSERT with VALUES clause will add a single row to the database table. The VALUES phrase contains the values \u200b\u200bof this data.

Subqueries can be used instead of the VALUES clause. A subquery INSERT adds the rows returned by the subquery to the table. The database server processes the subquery and inserts all returned rows into the table. The server does not insert rows if the subquery does not select them.

• subquery_1 - a subquery that the server processes in the same way as the view
• subquery_2 is a subquery that returns the rows being inserted into the table. The list of this subquery must have the same number of columns as the list of INSERT columns.

Subqueries are hardly used in MySQL database.

Examples of sql INSERT INTO query in MySQL database

Insert new rows into the MySQL database with the INSERT INTRO command.

First example.

Insert new rows into table table_name.

INSERT INTO table_name VALUES ('2', '145', '1', 'name');

This means that we want to insert into the columns the values \u200b\u200b2,145,1, name into table_name. Since the columns are not specified, the values \u200b\u200bwill be filled in all columns of the table.

Example two.

Inserting information into the required (specified) columns of the table table_name.

INSERT INTO table_name (client_customer, client_subclient, client_mail) VALUES (‘name1 ′,’ subname1 ′, ’ [email protected]′), (‘Name2 ′,’ subname2 ′, ’ [email protected]′), (‘Name3 ′,’ subname3 ′, (’ [email protected]′);

Igor Serov specially for the site "".

In addition to the SELECT statement discussed earlier, Data Manipulation Language (DML) contains three other statements: INSERT, UPDATE, and DELETE. Like the SELECT statement, these three statements operate on either tables or views. This article covers the INSERT statement and the other two statements are discussed in the next article.

INSERT statement inserts rows (or parts of rows) into a table. There are two different forms of this instruction:

INSERT tab_name [(col_list)] DEFAULT VALUES | VALUES ((DEFAULT | NULL | expression) [, ... n]) INSERT INTO tab_name | view_name [(col_list)] (select_statement | execute_statement) Syntax Conventions

The first form of the statement allows you to insert one row (or part of it) into the table. And the second form of INSERT allows you to insert into a table the result set of a SELECT statement or a stored procedure executed through an EXECUTE statement. The stored procedure must return data to be inserted into the table. When used with an INSERT statement, a SELECT statement can select values \u200b\u200bfrom a different or from the same table into which the data is inserted, as long as the data types of the corresponding columns are compatible.

For both forms, the data type of each inserted value must be compatible with the data type of the corresponding table column. All string and temporary data must be enclosed in quotation marks; numeric values \u200b\u200bdo not need to be quoted.

Insert one line

For both forms of the INSERT statement, an explicit list of columns is optional. Not listing the columns is equivalent to specifying all the columns in the table.

DEFAULT VALUES parameter inserts default values \u200b\u200bfor all columns. Columns with the TIMESTAMP data type or IDENTITY property are inserted by default with values \u200b\u200bthat are automatically generated by the system. For columns of other data types, the corresponding non-zero default is inserted, if any, or NULL, otherwise. If NULL values \u200b\u200bare not allowed for a column and no default value has been defined for the column, the INSERT statement fails and a message is displayed.

The example below shows inserting rows into the Employee table of the SampleDb database, demonstrating the use of an INSERT statement to insert a small amount of data into the database:

USE SampleDb; INSERT INTO Employee VALUES (34990, "Andrey", "Batonov", "d1"); INSERT INTO Employee VALUES (38640, "Alexey", "Vasin", "d3");

There are two different ways to insert values \u200b\u200binto a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts a NULL value into the appropriate column:

USE SampleDb; INSERT INTO Employee VALUES (34991, "Andrey", "Batonov", NULL);

To insert values \u200b\u200binto some (but not all) of the columns in a table, you usually need to explicitly specify those columns. Unspecified columns must either be nullable or have a default value defined.

USE SampleDb; INSERT INTO Employee (Id, FirstName, LastName) VALUES (34992, "Andrey", "Batonov");

The previous two examples are equivalent. In the Employee table, the only column that allows NULL values \u200b\u200bis DepartmentNumber, and for all other columns this value was denied by the NOT NULL clause in the CREATE TABLE statement.

Order of values \u200b\u200bin vALUES offer INSERT statements may differ from the order specified in the CREATE TABLE statement. In this case, their order must match the order in which the corresponding columns are listed in the column list. Below is an example of inserting data in a different order from the original:

USE SampleDb; INSERT INTO Employee (DepartamentNumber, LastName, Id, FirstName) VALUES ("d1", "Batonov", 34993, "Andrey");

Insert multiple lines

The second form of the INSERT statement inserts one or more rows selected by the subquery into the table. The example below shows how to insert rows into a table using the second form of the INSERT statement. In this case, a query is performed to select numbers and names of departments located in Moscow, and the resulting result set is loaded into a new table created earlier.

The new MoscowDepartment table created in the example above has the same columns as the existing Department table, except for the missing Location column. The subquery in the INSERT statement selects all rows in the Department table for which the Location column is Moscow, which is then inserted into the new table that was created at the beginning of the query.

The example below shows another way to insert rows into a table using the second form of the INSERT statement. In this case, a request is made to select personnel numbers, project numbers and project start dates for all employees with the "Manager" position who work on the p2 project, followed by loading the resulting result set into a new table created at the beginning of the query:

USE SampleDb; CREATE TABLE ManagerTeam (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, EnterDate DATE); INSERT INTO ManagerTeam (EmpId, ProjectNumber, EnterDate) SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE Job \u003d "Manager";

Before inserting rows using the INSERT statement, the MoscowDepartment and ManagerTeam tables (in the examples above) were empty. If the table already existed and contained rows with data, then new rows would be added to it.

Command adds rows to table or a main table view.

Sql INSERT Command Syntax

Insert Command Syntax

• schema - authority identifier, usually the same as the name of some user
• table view - the name of the table into which the rows are to be inserted; if view is specified, then rows are inserted into the main view table
• subquery_1 - a subquery that the server handles in the same way as a view
• column - a column of a table or view, into which a value from a phrase is entered for each inserted row VALUES or a subquery; if one of the table columns is omitted from this list, the column value for the inserted row is the default column value that was defined when the table was created. If the column list is omitted entirely, the sentence VALUES or the query should define values \u200b\u200bfor all columns in the table
• VALUES - defines a string of values \u200b\u200bto be inserted into a table or view; value must be specified in the sentence VALUES for each column in the column list
• subquery_2 - a subquery that returns the rows inserted into the table; the selective list of this subquery must have the same number of columns as in the assertion column list

Statement with the phrase VALUES adds a single row to the table. This string contains the values \u200b\u200bdefined by the phrase VALUES.
Approval with subquery instead of the phrase VALUES appends all rows returned by the subquery to the table. The server processes subquery and inserts each returned row into the table. If the subquery does not select any rows, the server does not insert any rows into the table.
Subquery can refer to any table or view, including the target of the statement ... The server assigns values \u200b\u200bto fields in new rows based on the internal position of the columns in the table and the order of the phrase values VALUES or in the query selection list. If any columns are missing from the column list, the server assigns them the default values \u200b\u200bthat were defined when the table was created. If any of these columns have a NOT NULL constraint, the server returns an error indicating that the constraint was violated and cancels the INSERT statement.
When an INSERT statement is issued, any INSERT trigger defined on the table is enabled.

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 command below copies the data of company employees whose commissions exceed 25% of income into the bonus table:

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

INSERT INTO Example 3
If you need to insert NULL-value, you must specify it as a normal value like this:

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

INSERT INTO Example 4
The command can be used to retrieve values \u200b\u200bfrom one table and place them in another using a query. To do this, it is enough to replace the sentence VALUES to the corresponding request:

INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city \u003d ‘London’;

MySQL INSERT

To insert new rows into MySQL database use iNSERT command, command examples are given below:
INSERT INTO Example 1.
Inserting a new row into table table_name.

INSERT INTO

INSERT INTO Example 2.
Inserting a new row into the table table_name, indicating the insertion of data into the columns we need.

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

In the database MySQL it is possible to insert many new lines using one command.
INSERT INTO Example 3.
Inserting multiple rows into table table_name.

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

The SQL statement INSERT INTO and INSERT SELECT are used to insert new rows into a table. There are two ways to use instructions:

1. Values \u200b\u200bonly: The first method is for specifying only the data values \u200b\u200bto be inserted without the column names.

Syntax:

INSERT INTO table_name VALUES (value1, value2, value3, ...); table_name: the name of the table. value1, value2, ..: values \u200b\u200bof first column, second column, ... for new record

1. Column names and values: The second method specifies the column names and row values \u200b\u200bto insert:

Syntax:

INSERT INTO table_name (column1, column2, column3, ..) VALUES (value1, value2, value3, ...); table_name: the name of the table. column1: name of first column, second column ... value1, value2, ..: values \u200b\u200bof first column, second column, ... for new record

Requests:

Method 1 ( insert only values):

INSERT INTO Student VALUES ("5", "HARSH", "WEST BENGAL", "8759770477", "19");

Result:

After using INSERT INTO SELECT, the Student table will now look like this:

Method 2 ( insert values \u200b\u200bonly into specified columns):

INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ("5", "PRATIK", "19");

Result:

The Student table will now look like this:

Note that columns for which no values \u200b\u200bare specified are set to null.

Using SELECT in an INSERT INTO statement

You can use the MySQL INSERT SELECT statement to copy rows from one table and insert them into another.

Using this operator is similar to using INSERT INTO. The difference is that a SELECT statement is used to select data from another table. Following are the different ways to use INSERT INTO SELECT:

• Insert all columns of a table: You can copy all table data and paste it into another table.

Syntax:

INSERT INTO first_table SELECT * FROM second_table; first_table: name of the first table. second_table: name of the second table.

We used a SELECT statement to copy data from one table and an INSERT INTO statement to insert it into another.

• Insert individual table columns... You can only copy the columns of a table that you want to paste into another table.

Syntax:

INSERT INTO first_table (column_names1) SELECT column_names2 FROM second_table; first_table: The name of the first table. second_table: name of the second table. column_names1: column names separated by comma (,) for table 1.column_names2: column names separated by comma (,) for table 2.

We used a SELECT statement to copy data only from the selected columns of the second table and a MySQL INSERT INTO SELECT statement to insert it into the first table.

• Copying specific rows from a table... You can copy specific rows from a table for later insertion into another table using a WHERE clause with a SELECT statement. In this case, you need to use the appropriate condition in the WHERE.

Syntax:

Table 2: LateralStudent

Requests:

Method 1 ( insert all rows and columns):

INSERT INTO Student SELECT * FROM LateralStudent;

Result:

This query will insert all the data from the LateralStudent table into the Student table. After applying SQL INSERT INTO SELECT, the Student table will look like this:

Method 2 ( inserting individual columns):

INSERT INTO Student (ROLL_NO, NAME, Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;

Result:

This query will insert data from the ROLL_NO, NAME, and Age columns of the LateralStudent table into the Student table. The rest of the columns in the Student table will be set to null. After applying SQL INSERT SELECT, the table will look like this:

• Selecting specific rows for insertion:

Result:

This query will only select the first row from the LateralStudent table to insert into the Student table. After applying INSERT SELECT, the table will look like this.

The INSERT statement inserts new records into the table. In this case, the values \u200b\u200bof the columns can be literal constants, or be the result of a subquery. In the first case, a separate INSERT statement is used to insert each row; in the second case, as many rows will be inserted as returned by the subquery.

The syntax for the operator is as follows:

INSERT INTO [(, ...)]

(VALUES (, ...))

1. | (DEFAULT VALUES)

As you can see from the syntax provided, the list of columns is optional (as indicated by the square brackets in the syntax description). If it is absent, the list of inserted values \u200b\u200bmust be complete, that is, provide values \u200b\u200bfor all columns of the table. However, the order of the values \u200b\u200bmust match the order specified by the CREATE TABLE statement for the table into which rows are inserted. In addition, these values \u200b\u200bmust be of the same data type as the columns in which they are entered. As an example, consider inserting a row into the Product table created with the following CREATE TABLE statement:

CREATE TABLE product

maker char (1) NOT NULL,

model varchar (4) NOT NULL,

type varchar (7) NOT NULL

Suppose you want to add to this table model PC 1157 from manufacturer B. This can be done with the following operator:

INSERT INTO Product

VALUES ("B", 1157, "PC");

If you specify a list of columns, you can change the "natural" order of their following:

INSERT INTO Product (type, model, maker)

VALUES ("PC", 1157, "B");

It would seem that this is a completely unnecessary feature, which only makes the design more cumbersome. However, it wins if the columns have default values. Consider the following table structure:

CREATE TABLE product_D

maker char (1) NULL,

model varchar (4) NULL,

type varchar (7) NOT NULL DEFAULT "PC"

Note that here the values \u200b\u200bof all columns have default values \u200b\u200b(the first two are NULL and the last column is type - PC). Now we could write:

INSERT INTO Product_D (model, maker)

VALUES (1157, "B");

In this case, the missing value will be replaced by the default value PC when inserting a row. Note that if a default value is not specified for a column in the CREATE TABLE statement, and a NOT NULL constraint is not specified to prohibit the use of NULL in a given table column, then the default value of NULL is assumed.

The question arises: is it possible not to specify the list of columns and, nevertheless, use the default values? The answer is yes. To do this, instead of explicitly specifying a value, use the DEFAULT reserved word:

INSERT INTO Product_D

VALUES ("B", 1158, DEFAULT);

Since all columns have default values, one could write to insert a row with default values:

INSERT INTO Product_D

VALUES (DEFAULT, DEFAULT, DEFAULT);

However, for this case there is a special DEFAULT VALUES construction (see operator syntax), with which the above operator can be rewritten as

INSERT INTO Product_D DEFAULT VALUES;

Note that when inserting a row into a table, all restrictions imposed on this table are checked. These can be primary key or unique index constraints, CHECK constraints, and referential integrity constraints. If any constraint is violated, the row insertion will be rejected. Let's now look at the use case for a subquery. Suppose we need to insert into the Product_D table all rows from the Product table related to models of personal computers (type \u003d ‘PC’). Since the values \u200b\u200bwe need are already in some table, the formation of inserted rows manually, firstly, is ineffective, and, secondly, it can make input errors. Using a subquery solves these problems:

The use of the "*" symbol in the subquery is justified in this case, since the order of the columns is the same for both tables. If this were not the case, a list of columns would have to be applied, either in the INSERT statement, in a subquery, or both, which would match the order of the columns:

Here, as before, not all columns can be specified if you want to use the existing default values, for example:

In this case, the default value PC for all inserted rows will be substituted into the type column of the Product_D table.

Note that when using a subquery containing a predicate, only those rows for which the predicate value is TRUE (not UNKNOWN!) Will be inserted. In other words, if the type column in the Product table were nullable, and that value was present in a number of rows, then those rows would not be inserted into the Product_D table.

To overcome the limitation of inserting a single row in an INSERT statement when using the row constructor in the VALUES clause, you can artificially use a subquery that forms a row with a UNION ALL clause. So if we need to insert multiple rows using one INSERT statement, we can write:

INSERT INTO Product_D

SELECT "B" AS maker, 1158 AS model, "PC" AS type

UNION ALL

SELECT "C", 2190, "Laptop"

UNION ALL

SELECT "D", 3219, "Printer";

Using UNION ALL is preferable to UNION even if no duplicate strings are guaranteed, since then no duplicate check will be performed.

It should be noted that inserting multiple tuples using the string constructor is already implemented in A relational database management system (DBMS) developed by Microsoft Corporation. Structured Query Language) is a universal computer language used to create, modify and manipulate data in relational databases.SQL Server 2008. With this possibility in mind, the last query can be rewritten as:

INSERT INTO Product_D VALUES

("B", 1158, "PC"),

("C", 2190, "Laptop"),