home Browsers SQL insert queries. SQL query INSERT INTO - fill the database with information. Operator work example

SQL insert queries. SQL query INSERT INTO - fill the database with information. Operator work example

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	south park	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.

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

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 columns from one table to another existing table:

INSERT INTO table2
SELECT * FROM table1;

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

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

Demo database

In this tutorial, we will be using the well-known Northwind database.

Below is a selection from the "Customers" table:

User ID	Client Name	The contact person	Address	city	Postcode	Country
1	Alfred Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados and 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 a selection from the "Suppliers" table:

SQL INSERT INTO SELECT, Examples

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

Copy only German suppliers in "Customers" .

sql INSERT INTO query makes sense when the 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 query INSERT INTO - 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's not mandatory. Needed to protect primary keys when editing a table. Otherwise, if during editing there is a duplication of primary keys, then when inserting the IGNORE option, the first row with the primary key will remain in the modified table, Other primary keys are deleted. By default, this option is omitted.

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

Query line: INSERT with the VALUES clause will add a single row to the database table. The VALUES clause contains the values of this data.

Subqueries can be used instead of the VALUES clause. INSERT with 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 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 that are inserted into the table. This subquery's list must have the same number of columns as the list of INSERT columns.

Subqueries are practically not used in the MySQL database.

INSERT INTO sql query examples in MySQL database

Insert new rows into MySQL database with 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 columns in the table_name table. Since the columns are not specified, the values are filled in all the columns of the table.

Second example.

Inserting information into the desired (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 deals with 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 statement 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 the 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, the 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 quotation marks; Numeric values do not need to be enclosed in quotation marks.

Single line insert

For both forms of the INSERT statement, an explicit list of columns is optional. The absence of a list of columns is equivalent to specifying all the columns of the table.

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

The following example inserts rows into the Employee table in the SampleDb database, demonstrating how to use the 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, "Aleksey", "Vasin", "d3");

There are two different ways inserting values into new line. The INSERT statement in the example below explicitly uses keyword NULL and inserts the 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. Columns not specified must either allow null values or must 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 for all other columns, this value was prohibited by the NOT NULL clause in the CREATE TABLE statement.

Order of values in sentence VALUES INSERT statements may differ from the order specified in the CREATE TABLE statement. In such a case, their order must match the order in which the corresponding columns are listed in the column list. The following 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 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 the numbers and names of departments located in Moscow, and loading the resulting set 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 value of the Location column 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 the p2 project, and then load the resulting 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 data rows, then new rows would be added to it.

In the previous sections, we considered the work of obtaining 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 like: CREATE- creates a table ALTER- changes the structure of the table, DROP- deletes a table or field, INSERT- adds data to the table. Let's start our acquaintance with this group of operators from the operator INSERT.

1. Adding whole 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 string
- add query results.

So, to add a new row to the 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 the 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 explicitly marked the table column names. Using this syntax, we can skip some columns. This means that you enter a value for some columns but don't provide values for others. For example:

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

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

3. Adding selected data

In the previous examples, we inserted data into tables by writing 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. To do this, SQL has a structure like this: INSERT INTO ... SELECT .... This design allows you to simultaneously select data from one table and insert them into another. Suppose 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 different data. 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 that the value of the internal keys is 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 of their arrangement is important for 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 anyway 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, it becomes necessary to create copies of any tables for the purpose of backup or modification. To make a full copy of a table in SQL, a separate statement is provided 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 construct copies the data to the new table. It can also be said that the first construct imports data, while the second construct exports. When using the structure SELECT ... INTO ... FROM ... the following should be taken into account:

- you can use any sentences in the operator SELECT, such as GROUP BY and HAVING
- join can be used to add data from multiple tables
- data can only be added to one table, regardless of how many tables they were taken from.

Just about the complex. Programs. Iron. Internet. Windows