home › Data recovery › Insert into syntax. SQL INSERT INTO SELECT statement. Examples of sql query INSERT INTO in a MySQL database

Insert into syntax. SQL INSERT INTO SELECT statement. Examples of sql query INSERT INTO in a MySQL database

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".

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.

Subqueries can be specified instead of the VALUES phrase. 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 "".

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.

What is INSERT INTO?

The main goal of database systems is to store data in the tables. The data is usually supplied by application programs that run on top of the database. Towards that end, SQL has the INSERT command that is used to store data into a table. The INSERT command creates a new row in the table to store data.

Basic syntax

Let's look at the basic syntax of the SQL INSERT command shown below.

INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);

INSERT INTO `table_name` is the command that tells MySQL server to add new row into a table named `table_name`.
(column_1,column_2,...) specifies the columns to be updated in the new row
VALUES (value_1,value_2,...) specifies the values to be added into the new row

When supplying the data values to be inserted into the new table, the following should be considered while dealing with different data types.

String data types- all the string values should be enclosed in single quotes.
Numeric data types- all numeric values should be supplied directly without enclosing them in single or double quotes.
Date data types- enclose date values in single quotes in the format "YYYY-MM-DD".

Example:

Suppose that we have the following list of new library members that need to be added into the database.

Full names	Date of Birth	gender	Physical address	Postal address	Contact number
Leonard Hofstadter		Male	Woodcrest		0845738767
Sheldon Cooper		Male	Woodcrest		0976736763
Rajesh Koothrappali		Male	Fairview		0938867763
Leslie Winkle	14/02/1984	Male			0987636553
Howard Wolowitz	24/08/1981	Male	South Park	P.O. Box 4563	0987786553

Lets" INSERT data one by one. We will start with Leonard Hofstadter. We will treat the contact number as a numeric data type and not enclose the number in single quotes.

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ("Leonard Hofstadter","Male","Woodcrest",0845738767);

Executing the above script drops the 0 from Leonard"s contact number. This is because the value will be treated as a numeric value and the zero (0) at the beginning is dropped since it"s not significant.

In order to avoid such problems, the value must be enclosed in single quotes as shown below -

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ("Sheldon Cooper","Male","Woodcrest", "0976736763");

In the above case , zero(0) will not be dropped

Changing the order of the columns has no effect on the INSERT query as long as the correct values have been mapped to the correct columns.

The query shown below demonstrates the above point.

INSERT INTO `members` (`contact_number`,`gender`,`full_names`,`physical_address`)VALUES ("0938867763","Male","Rajesh Koothrappali","Woodcrest");

The above queries skipped the date of birth column, by default MySQL will insert NULL values in columns that are skipped in the INSERT query.

Let's now insert the record for Leslie which has the date of birth supplied. The date value should be enclosed in single quotes using the format "YYYY-MM-DD".

INSERT INTO `members` (`full_names`,`date_of_birth`,`gender`,`physical_address`,`contact_number`) VALUES ("Leslie Winkle","1984-02-14","Male","Woodcrest"," 0987636553");

All of the above queries specified the columns and mapped them to values in the insert statement. If we are supplying values for ALL the columns in the table, then we can omit the columns from the insert query.

INSERT INTO `members` VALUES (9,"Howard Wolowitz","Male","1981-08-24","SouthPark","P.O. Box 4563", "0987786553", "lwolowitzemail.me");

Let's now use the SELECT statement to view all the rows in the members table.SELECT * FROM `members`;

membership_number	full_names	gender	date_of_birth	physical_address	postal_address	contct_number	email
1	Janet Jones	Female	21-07-1980	First Street Plot No. 4	Private Bag	0759 253 542	This email address is being protected from spambots. You need JavaScript enabled to view it.
2	Janet Smith Jones	Female	23-06-1980	Melrose 123	NULL	NULL	This email address is being protected from spambots. You need JavaScript enabled to view it.
3	Robert Phil	Male	12-07-1989	3rd Street 34	NULL	12345	This email address is being protected from spambots. You need JavaScript enabled to view it.
4	Gloria Williams	Female	14-02-1984	2nd Street 23	NULL	NULL	NULL
5	Leonard Hofstadter	Male	NULL	Woodcrest	NULL	845738767	NULL
6	Sheldon Cooper	Male	NULL	Woodcrest	NULL	976736763	NULL
7	Rajesh Koothrappali	Male	NULL	Woodcrest	NULL	938867763	NULL
8	Leslie Winkle	Male	14-02-1984	Woodcrest	NULL	987636553	NULL
9	Howard Wolowitz	Male	24-08-1981	SouthPark	P.O. Box 4563	987786553	This email address is being protected from spambots. You need JavaScript enabled to view it.

Notice the contact number for Leonard Hofstadter has dropped the zero (0) from the contact number. The other contact numbers have not dropped the zero (0) at the beginning.

Inserting into a Table from another Table

The INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below.

INSERT INTO table_1 SELECT * FROM table_2;

Let"s now look at a practical example, we will create a dummy table for movie categories for demonstration purposes. We will call the new categories table categories_archive. The script shown below creates the table.

CREATE TABLE `categories_archive` (`category_id` int(11) AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`))

Execute the above script to create the table.

Let's now insert all the rows from the categories table into the categories archive table. The script shown below helps us to achieve that.

INSERT INTO `categories_archive` SELECT * FROM `categories`;

Executing the above script inserts all the rows from the categories table into the categories archive table. Note the table structures will have to be the same for the script to work. A more robust script is one that maps the column names in the insert table to the ones in the table containing the data .

The query shown below demonstrates its usage.

INSERT INTO `categories_archive`(category_id,category_name,remarks) SELECT category_id,category_name,remarks FROM `categories`;

Executing the SELECT query

SELECT * FROM `categories_archive`

gives the following results shown below.

category_id	category_name	remarks
1	Comedy	Movies with humor
2	Romantic	Love stories
3	Epic	Story ancient movies
4	Horror	NULL
5	Science Fiction	NULL
6	Thriller	NULL
7	Action	NULL
8	Romantic Comedy	NULL
9	Cartoons	NULL
10	Cartoons	NULL

Summary

The INSERT command is used to add new data into a table
The date and string values should be enclosed in single quotes.
The numeric values do not need to be enclosed in quotes.
The INSERT command can also be used to insert data from one table into another.

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 inserting values into new line. 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.

Popular in the category: