SQL INSERT INTO SELECT statement. Transact-SQL - data insertion Adding part of rows

Using SQL, you can copy information from one table to another.

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

SQL INSERT INTO SELECT statement,

INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are not changed.

SQL INSERT INTO SELECT, Syntax

We can copy all the columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

Demo version of the database

In this tutorial we will use the well known Northwind database.

Below is a selection from the "Customers" table:

User ID	Client's name	The contact person	Address	city	Postcode	A country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitucion 2222	Mexico D.F.	05021	Mexico
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	Mexico D.F.	05023	Mexico

And the selection from the "Suppliers" table:

SQL INSERT INTO SELECT, Examples

Copying only a few columns from "Suppliers" Into "Customers":

Copying only German suppliers to "Customers".

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 parentheses after the VALUES keyword 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.

In addition to the SELECT statement discussed earlier, the 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 discusses 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:

The first form of the instruction allows you to insert one row (or part of it) into the table. And the second form of the INSERT statement allows you to insert into a table the result set of a SELECT statement or a stored procedure executed by 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 from a different or the same table into which the data is being 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 quotes; Numeric values do not need to be enclosed in quotation marks.

Inserting a single row

For both forms of the INSERT statement, specifying the column list explicitly is optional. Not listing columns is the same as specifying all columns in the table.

DEFAULT VALUES parameter inserts default values for all columns. Columns with the TIMESTAMP data type or IDENTITY property are inserted by default with values that are automatically generated by the system. For columns of other data types, the corresponding non-null default value is inserted if available, or NULL otherwise. If a column does not allow null values and does not have a default value defined, the INSERT statement fails and a message is displayed.

The example below inserts rows into the Employee table in 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 into a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts a NULL value into the corresponding column:

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

To insert values into some (but not all) columns of a table, you usually need to explicitly specify those columns. Unspecified columns must either allow NULL values 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 is the DepartmentNumber column, and all other columns were disabled by the NOT NULL clause in the CREATE TABLE statement.

Order of values in 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");

Inserting multiple rows

The second form of the INSERT statement inserts one or more rows selected by a 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 executed to select the 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 value is "Moscow", which are then inserted into the new table 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 query is executed to select personnel numbers, project numbers, and project start dates for all employees with the position “Manager” who work on project p2 and then load 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 = "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.

In previous sections, we looked at the work of retrieving data from pre-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 in SQL There are operators such as: CREATE- creates a table, ALTER- changes the table structure, DROP- deletes a table or field, INSERT- adds data to the table. Let's start getting acquainted with this group of operators from the operator INSERT.

1. Adding entire lines

As the name suggests, the operator INSERT used to insert (append) rows to a database table. Adding can be done in several ways:

- add one full line
- add part of a line
- add query results.

So, to add a new row to a table, we need to specify the table name, list the column names and specify the value for each column using the construct INSERT INTO table_name (field1, field2 ...) VALUES (value1, value2...). Let's look at 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 the values in the parameter VALUES.

2. Adding part of the lines

In the previous example, when using the operator INSERT we have explicitly marked the table column names. Using this syntax, we can skip some columns. This means that you enter values for some columns but do not provide them for others. For example:

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

In this example, we did not specify a value for two columns Address And Country. You can exclude some columns from the statement INSERT INTO, if this allows the table definition. In this case, one of the conditions must be met: this column is defined as valid NULL(absence of any value) or the specified default value in the table definition. This means that if no value is specified, the default value will be used. If you are missing a column from a table that does not allow values to appear in its rows NULL and does not have a default value defined, the DBMS will generate an error message and the row will not be added.

3. Adding selected data

In the previous example, we inserted data into tables by entering them manually in the query. However, the operator INSERT INTO allows us to automate this process if we want to insert data from another table. For this purpose in SQL there is such a construction as INSERT INTO ... SELECT .... This design allows you to simultaneously select data from one table and insert it into another. Let's assume we have another table Sellers_EU with a list of sellers of our goods 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) SELECTID, Address, City, Seller_name, Country FROM Sellers_EU

You need to pay attention so that the values of internal keys are not repeated (field ID), otherwise an error will occur. Operator SELECT may also include suggestions WHERE to filter data. It should also be noted that the DBMS does not pay attention to the names of the columns contained in the statement SELECT, only the order in which they are arranged is important to her. Therefore, the data in the first specified column that was selected due to SELECT, will be filled in the first column of the table in any case Sellers, specified after the operator INSERT INTO, 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 full copy of a table, SQL provides a separate statement SELECT INTO. For example, we need to create a copy of the table Sellers, you will need to write the request as follows:

SELECT * INTO Sellers_new FROM Sellers

Unlike the previous design INSERT INTO ... SELECT ... When data is added to an existing table, the design copies the data to the new table. You can also say that the first construct imports data, and the second exports. When using the design SELECT ... INTO ... FROM ... The following should be considered:

- you can use any sentences in the operator SELECT, such as GROUP BY And HAVING
- you can use a join to add data from multiple tables
- data can only be added to one table, no matter how many tables it was taken from.

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 saved query.
value1, value2	Values that will be added to specific fields of 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. You must separate values with a comma and enclose 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 the 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 add 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.