Sounds and Music 

SQL query INSERT INTO - fill the database with information. T-SQL Basics. DML Oracle insert into syntax

sql query INSERT INTO makes sense when a database table has been 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 query INSERT INTO - query syntax

sql query INSERT INTO has the following syntax:

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

You can insert an IGNORE option between INSERT and INTRO. It is not required. Needed to protect primary keys when editing a table. Otherwise, if duplication of primary keys occurs during editing, then when inserting the IGNORE option, the first row with the primary key will remain in the table being edited. Other primary keys will be deleted. By default, we omit this option.

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

The line in the query: INSERT with the VALUES phrase will allow you to add a single row to the database table. The VALUES clause contains the values ​​of this data.

Instead of the VALUES phrase, subqueries can be specified. INSERT with a subquery 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 unless the subquery selects them.

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

Subqueries are practically not used in a MySQL database.

Examples of sql query INSERT INTO in a MySQL database

We insert new rows into the MySQL database using 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 the values ​​2,145,1,name into the table table_name columns. Since the columns are not specified, the values ​​are filled in all columns of the table.

Example two.

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

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 especially for the site "".

This statement adds one or more records to a table (performs an append query).

Syntax

Request to add multiple records:

INSERT INTO final_object [(field1[, field2[, ...]])]
SELECT [ source.]field1[, field2[, ...]
FROM table_expression

Request to add one record:

INSERT INTO final_object [(field1[, field2[, ...]])]
VALUES ( field1[, field2[, ...])

The INSERT INTO statement consists of the following elements:

Part

Description

final_object

The name of the table or query where records are added.

field1, field2

After the argument final_object- names of fields to which data is added; after the argument source- names of the fields from which data is extracted.

external_database

Path to external database. For a description of the path, see the article on the IN clause.

source

The name of the table or query from which records are copied.

table_expression

One or more table names from which you want to retrieve records. This argument can be the name of an individual table, a result expression constructed using an INNER JOIN, LEFT JOIN, or RIGHT JOIN, or a stored query.

value1, value2

Values ​​that will be added to specific fields in the new record. Each value is inserted into the field corresponding to its position in the list: value1 added to field1 new entry, value2- V field2 etc. It is necessary to separate the values ​​with a comma and conclude text fields in quotation marks (" ").

Notes

The INSERT INTO statement can add a single record to a table using the syntax above. In this case, you specify names and values ​​for each field in the record. You must specify all the fields in the record to which values ​​are assigned, and corresponding values. If you do not specify a field value, it will be assigned the default value or NULL. Records are added to the end of the table.

The INSERT INTO statement can also be used to add a set of records from another table or query using the SELECT... FROM clause as shown above (see Query Syntax for Adding Multiple Records). In this case, the SELECT clause specifies the fields to be added to the specified final_object.

Source or final_object can be a table or a query. When a query is given, the Microsoft Access database engine adds records to all the tables it returns.

Using the INSERT INTO statement is optional. If specified, it must precede the SELECT statement.

If the target table contains a primary key, ensure that the values ​​you add to one or more of the primary key fields are unique and distinct from NULL; otherwise the entries will not be added.

If records are added to a table with a Counter field and you want to renumber them, do not include the Counter field in the query. Include the Counter field in the query if you want to preserve the original values ​​from the field.

You can add records to a table in another database using the IN clause.

To create a table, use the SELECT... INTO statement to query to create the table.

Before you run an add query, use a select query with the same selection criteria to use the results to determine which records will be added.

An append query copies records from one or more tables to another table. In this case, the tables containing the added records remain unchanged.

Instead of adding records from another table, you can set the value of each field in a separate new record using the VALUES clause. If a field list is omitted, the VALUES clause must include the corresponding values ​​for each table field; otherwise, the INSERT operation will fail. Use the INSERT INTO statement along with the VALUES clause for each additional record that you want to create.

We learned how to create a database and tables in these databases, but the tables turned out to be “empty”. And in this article I want to teach you how to enter data into these tables and the SQL statement called “INSERT” will help us with this. Let's get started:

INSERT- operator SQL language, which allows you to add rows to a table, filling them with values. Values ​​can be inserted by enumeration using the word values ​​and listing them in parentheses separated by commas.

The SQL INSERT statement specifying columns has the following syntax:

INSERT INTO ([, ... ]) values ​​(,...);

Also, values ​​can be written without specifying columns:

INSERT INTO values ​​(,...);

Values ​​can also be recorded using SQL statement SELECT (we will study the SELECT statement a little later):

INSERT INTO SELECT Column name,... FROM table name

Using the operator SQL SELECT You can insert more than one record. In case there are no values ​​specified for any table fields, they will be replaced with the default value null.

Operator work example

There is a table “Planets”. Using the SQL INSERT statement, you need to add a record, assigning the following values ​​to the table fields - ID: 4; PlanetName: Venus; Radius: 6051; SunSeason: 243; OpeningYear: 1610; HavingRings: No; Opener: Galileo Galilei;

INSERT INTO Planets (ID, PlanetName, Radius, SunSeason, OpeningYear, HavingRings, Opener) VALUES (4, "Venus", 6051, 243, 160, "No", "Galileo Galilei");

After executing the command, the Planets table will look like this (as you can see, new line and it's very simple):



You probably noticed that I showed this example based on the first syntax. Where you need to write each column name, but the same example can be written differently:

INSERT INTO Planets VALUES (4, "Venus", 6051, 243, 160, "No", "Galileo Galilei");

This example will work if you need to insert under each column and you know the order of these columns. Otherwise, the data may end up in the wrong column.

Here comes my little lesson on the INSERT statement in SQL. I hope everything is clear to you, if you have any questions, write and I will definitely answer.

Hi all! This article will discuss how you can add data to table at Microsoft SQL Server If you are already at least a little familiar with the T-SQL language, then you probably realized that now we will talk about the INSERT statement, as well as how it can be used to add data to a table.

Let's start, as usual, with a little theory.

INSERT statement in T-SQL

INSERT is a T-SQL instruction that is designed to add data to a table, i.e. creating new records. This instruction can be used both to add a single row to a table and to bulk insert data. The INSERT statement requires permission to insert data ( INSERT) to the target table.

There are several ways to use the INSERT statement on the piece of data that needs to be inserted:

  • Transfer specific values to insert;
  • Specifying a data set as a SELECT query;
  • Specifying a data set in the form of a procedure call that returns tabular data.

Simplified syntax

INSERT [table] ( list of columns...) VALUES ( list of values, … ) Or SELECT sample request Or EXECUTE procedure

  • INSERT INTO is a command to add data to a table;
  • Table is the name of the target table into which you want to insert new records;
  • The column list is a list of column names of the table into which the data will be inserted, separated by commas;
  • VALUES is a table value constructor with which we specify the values ​​that we will insert into the table;
  • The list of values ​​is the values ​​that will be inserted, separated by commas. They are listed in the order that the columns appear in the column list;
  • SELECT is a query to select data to insert into a table. The result set that the query returns must match the list of columns;
  • EXECUTE is a procedure call to obtain data for insertion into a table. The result set that the stored procedure returns must match the list of columns.

This is roughly what the simplified syntax of the INSERT INTO statement looks like; in most cases, this is how you will add new records to tables.

The list of columns into which you will insert data does not need to be written, in which case their order will be determined based on the actual order of the columns in the table. You must remember this order when you specify values ​​to insert or write a query to select. Personally, I recommend that you still indicate a list of columns into which you plan to add data.

You should also remember that the list of columns and the list of values, respectively, must contain so-called required columns; these are those that cannot contain the NULL value. If you do not specify them, and the column does not have a default value, an error will occur.

I would also like to note that the data type of the values ​​that you will insert must match the data type of the column into which this value will be inserted, or at least support implicit conversion. But I advise you to control the data type ( format) values, both in the list of values ​​and in the SELECT query.

Enough theory, let's move on to practice.

Initial data

In order to add data to the table, we need the table itself, so let's create it and try to add records to it.

Note! All examples will be run in Microsoft SQL Server 2016 Express.

CREATE TABLE TestTable( IDENTITY(1,1) NOT NULL, (100) NOT NULL, NOT NULL)

Our test table will contain a list of products with prices.

Also in the examples we will use a procedure that returns a table value to add data to the table, so let's create that too.

CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END

For example, it will return data from the newly created TestTable table.

Note!

As you understand, reading this material implies having some knowledge of the T-SQL language, so if something is not clear to you, I recommend that you familiarize yourself with the following materials:

Example 1 – Adding a new record to a table using the table value constructor

First let's try adding one record and immediately look at the result, i.e. Let's write a request for a sample.

INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100) GO SELECT * FROM TestTable

You see that after the table name we listed the names of the columns to which we will add data, separated by commas, then we indicated the keyword VALUES and in brackets also, in the same order, separated by commas, we wrote the values ​​that we want to insert.

After the INSERT statement, I wrote a SELECT statement and separated them with a GO statement.

Now let's imagine that we need to add a few lines. We will write the following request for this.

INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100), ("Keyboard", 20), ("Monitor", 50) GO SELECT * FROM TestTable


Example 2 - Adding new rows to a table using a SELECT query

Very often there is a need to add a lot of data to a table, for example, based on a select query, i.e. SELECT. To do this, instead of VALUES, we just need to specify the request.

INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id >


IN in this example we wrote a SELECT query that returns data from the TestTable table, but not all of it, but only those with an ID greater than 2. And the result was inserted into the same TestTable table.

As an example of how you can add records to a table without specifying a list of columns, let's write another data insertion query that will do exactly the same thing as the query above, only it will not list the columns to insert.

INSERT INTO TestTable SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable


In this case, we are sure that in the TestTable table the first column is ProductName, and the second is Price, so we can afford to write it that way. But, again, in practice it is better to specify a list of columns.

If you noticed, in all the examples I did not specify the Id column, but we have it, no errors occurred, since this column has the IDENTITY property, it automatically generates identifiers, so inserting data into such a column simply cannot be done.

Example 3 - Adding new records to a table using a stored procedure

Now let's insert the data into the table that the stored procedure will return to us. The meaning here is the same, instead of VALUES and instead of a request we indicate a procedure call. But as you understand, the order and number of columns returned by the procedure must strictly match the list of columns to be inserted ( even if the column list is not specified).

INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable


Hope, this material helped you understand the instructions INSERT INTO, and that’s all I have for now!

Last update: 07/13/2017

To add data, use the INSERT command, which has the following formal syntax:

INSERT table_name [(column_list)] VALUES (value1, value2, ... valueN)

First comes the INSERT INTO expression, then in parentheses you can specify a comma-separated list of columns to which data should be added, and at the end, after the word VALUES, the values ​​to be added for the columns are listed in parentheses.

For example, suppose the following database was previously created:

CREATE DATABASE productsdb; GO USE productsdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL)

Let's add one line to it using the INSERT command:

INSERT Products VALUES ("iPhone 7", "Apple", 5, 52000)

After successful execution in SQL Server Management Studio, the message "1 row(s) affected" should appear in the message field:

It is worth considering that the values ​​for the columns in brackets after keyword VALUES are passed in the order in which they are declared. For example, in the CREATE TABLE statement above, you can see that the first column is Id. But since the IDENTITY attribute is specified for it, the value of this column is automatically generated and can be omitted. The second column represents ProductName, so the first value, the string "iPhone 7", will be passed to that column. The second value - the string "Apple" will be passed to the third column Manufacturer and so on. That is, the values ​​are passed to the columns as follows:

    ProductName: "iPhone 7"

    Manufacturer: "Apple"

Also, when entering values, you can specify the immediate columns to which the values ​​will be added:

INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ("iPhone 6S", 41000, "Apple")

Here the value is specified for only three columns. Moreover, now the values ​​are transmitted in the order of the columns:

    ProductName: "iPhone 6S"

    Manufacturer: "Apple"

For unspecified columns (in this case ProductCount), a default value will be added if the DEFAULT attribute is specified, or a NULL value. However, unspecified columns must be nullable or have a DEFAULT attribute.

We can also add several lines at once:

INSERT INTO Products VALUES ("iPhone 6", "Apple", 3, 36000), ("Galaxy S8", "Samsung", 2, 46000), ("Galaxy S8 Plus", "Samsung", 1, 56000)

In this case, three rows will be added to the table.

Also, when adding, we can specify that the column should have a default value using the DEFAULT keyword, or a NULL value:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ("Mi6", "Xiaomi", DEFAULT, 28000)

In this case, the default value for the ProductCount column will be used (if it is set, if it is not, then NULL).

If all columns have a DEFAULT attribute that specifies a default value, or are nullable, you can insert default values ​​for all columns:

INSERT INTO Products DEFAULT VALUES

But if we take the Products table, then such a command will fail with an error, since several fields do not have the DEFAULT attribute and at the same time do not allow the NULL value.