Transact-SQL - data insertion. SQL query INSERT INTO - fill the database with information Sql insert syntax

Last updated: 07/13/2017

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

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

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

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 should be borne in mind that the values for the columns in parentheses 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 set for it, the value of this column is automatically generated, and it can be omitted. The second column represents ProductName, so the first value, the string "iPhone 7", will be passed to that particular column. The second value is 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. And now the values are passed 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 set, or NULL. However, unspecified columns must be nullable or DEFAULT.

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 default value for the column is used using the DEFAULT keyword or NULL:

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

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

If all columns have a default DEFAULT attribute or are nullable, then you can insert default values for all columns:

INSERT INTO Products DEFAULT VALUES

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

This statement adds one or more records to the table (executes an append query).

Syntax

Request to add multiple records:

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

Request to add one record:

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

The INSERT INTO statement consists of the following elements:

Part	Description
destination_object	The name of the table or query to which records are added.
field1, field2	After the argument destination_object- the names of the fields to which the data is added; after the argument a source- the names of the fields from which the data is retrieved.
external_database	The path to the external database. For a description of the path, see the article on the IN clause.
a source	The name of the table or query from which the records are copied.
table_expression	One or more table names from which to retrieve records. This argument can be the name of an individual table, a result expression constructed using INNER JOIN, LEFT JOIN, or RIGHT JOIN, or a stored query.
value1, value2	Values to 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 and so on. Separate values with a comma and enclose text fields in quotation marks ("").

Remarks

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

You can also use INSERT INTO to add a recordset from another table or query using the SELECT ... FROM clause, as shown above (see the syntax for adding multiple records). In this case, the SELECT clause specifies the fields to be added to the specified destination_object.

A source or destination_object can be a table or a query. If a query is specified, the Microsoft Access Database Engine adds records to all tables it returns.

The use of the INSERT INTO statement is optional. If specified, it must precede the SELECT statement.

If the destination table contains a primary key, ensure that the values added to one or more of the primary key fields are unique and different from NULL; otherwise, no entries will be added.

If you are adding records to a table with a Counter field and you want to renumber them, do not include the Counter field in your query. Include the Counter field in your query if you want to keep 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 a SELECT ... INTO statement to get a query to create a table.

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

An append query copies records from one or more tables to another table. At the same time, the tables containing the added records remain unchanged.

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

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

Values Only: The first method is for specifying only the data values to 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 of first column, second column, ... for new record

Column names and values: The second method specifies the column names and row values to 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 of first column, second column, ... for new record

Requests:

Method 1 ( inserting only values):

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

Result :

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

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	9455123451	18
2	RAMESH	GURGAON	9562431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	Delhi	9156768971	18
3	SUJIT	ROHTAK	9156253131	20
2	RAMESH	GURGAON	9562431543	18
5	HARSH	WEST BENGAL	8759770477	19

Method 2 ( inserting values into specified columns only):

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

Result :

The Student table will now look like this:

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	9455123451	18
2	RAMESH	GURGAON	9562431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	Delhi	9156768971	18
3	SUJIT	ROHTAK	9156253131	20
2	RAMESH	GURGAON	9562431543	18
5	PRATIK	null	null	19

Note that columns with no values 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 the same as using INSERT INTO. The difference is that the SELECT statement is used to fetch data from another table. Below are the different ways using 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: the name of the first table. second_table: the 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 copy only those table columns 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: the 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

ROLL_NO	NAME	ADDRESS	PHONE	Age
7	SOUVIK	DUMDUM	9876543210	18
8	NIRAJ	NOIDA	9786543210	19
9	SOMESH	ROHTAK	9687543210	20

Requests:

Method 1 ( insert all rows and columns):

INSERT INTO Student SELECT * FROM LateralStudent;

Result :

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

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	9455123451	18
2	RAMESH	GURGAON	9562431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	Delhi	9156768971	18
3	SUJIT	ROHTAK	9156253131	20
2	RAMESH	GURGAON	9562431543	18
7	SOUVIK	DUMDUM	9876543210	18
8	NIRAJ	NOIDA	9786543210	19
9	SOMESH	ROHTAK	9687543210	20

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. For the rest of the columns of the Student table, null... After applying SQL INSERT SELECT, the table will look like this:

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	9455123451	18
2	RAMESH	GURGAON	9562431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	Delhi	9156768971	18
3	SUJIT	ROHTAK	9156253131	20
2	RAMESH	GURGAON	9562431543	18
7	SOUVIK	Null	null	18
8	NIRAJ	Null	null	19
9	SOMESH	Null	null	20

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 column values 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 (, ...))

| (DEFAULT VALUES)

As you can see from the syntax presented, the list of columns is optional (this is what square brackets(see syntax description). If it is absent, the list of inserted values must be complete, that is, provide values for all columns of the table. However, the order of the values must match the order specified by the CREATE TABLE statement for the table into which the rows are inserted. In addition, these values must 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 by 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 of all columns have default values (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 with 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 the syntax of the operator), with which the above operator can be rewritten as

INSERT INTO Product_D DEFAULT VALUES;

Note that when you insert a row into a table, all restrictions imposed on this table are checked. These can be primary key or unique index constraints, check constraints such as CHECK, and referential integrity constraints. If any constraint is violated, the row insertion will be rejected. Let's now consider 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 personal computers(type = ‘PC’). Since the values we 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, you can specify not all columns 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, the artificial technique of using a subquery that forms a row with a UNION ALL clause can be used. 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.Structured Query Language) is a universal computer language used to create, modify and manage data in relational databases. SQL Server 2008. Taking this opportunity into account, the last query can be rewritten as:

INSERT INTO Product_D VALUES

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

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

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