This statement adds one or more records to the table (performs an add query).
Query to add multiple entries:
INSERT INTO target_object [(field1[, field2[, ...]])]
SELECT[ source.]field1[, field2[, ...]
FROM table_expression
Request to add one entry:
INSERT INTO target_object [(field1[, field2[, ...]])]
VALUES ( field1[, field2[, ...])
The INSERT INTO statement consists of the following elements:
Part | Description |
|---|---|
|
target_object |
The name of the table or query where the records are added. |
|
field1, field2 |
After the argument target_object- the names of the fields to which the data is added; after the argument source- the names of the fields from which the data is retrieved. |
|
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 where the records are copied from. |
|
table_expression |
One or more table names from which to retrieve records. This argument can be a single table name, the result of an INNER JOIN, LEFT JOIN, or RIGHT JOIN expression, or a saved query. |
|
value1, value2 |
Values to 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 record, value2- V field2 etc. You must separate the values with a comma and enclose the text fields in quotation marks (" "). |
An INSERT INTO statement can add a single record to a table using the above syntax. In this case, names and values are specified for each field of the record. You must specify all fields of the record to which values are assigned and their corresponding values. If you don't specify a field value, it will be assigned a default value or NULL. Records are added to the end of the table.
You can also use the INSERT INTO statement to add a set of records from another table or query using the SELECT... FROM clause, as shown above (see Multiple Record Query Syntax). In this case, the SELECT clause specifies the fields to add to the specified target_object.
Source or target_object can be a table or a query. When a query is given, the Microsoft Access database engine adds records to all tables it returns.
The use of the INSERT INTO statement is optional. If present, it must precede the SELECT statement.
If the target table contains a primary key, make sure that the values added to one or more primary key fields are unique and different from NULL; otherwise, no entries will be added.
If records are added to a table with a Count field and you want to renumber them, do not include the Count field in your query. Include the "Counter" field in the 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 the SELECT... INTO statement to get a query to create a table.
Before running an append query, use a select query with the same selection criteria to determine which records will be added based on the results.
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 set the value of each field in a separate new record using the VALUES clause. If the list of fields 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.
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:
INSERT tab_name [(col_list)] DEFAULT VALUES | VALUES (( DEFAULT | NULL | expression ) [ ,...n]) INSERT INTO tab_name | view_name [(col_list)] (select_statement | execute_statement) Syntax Conventions
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 by 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.
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 to insert values into a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts the NULL value into the appropriate 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 for them.
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");
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 fetch personnel numbers, project numbers, and project start dates for all employees with the position "Manager" who work on the p2 project, and then loading 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.
Hi all! This article will discuss how you can add data to table in Microsoft SQL Server, if you are already at least a little familiar with the T-SQL language, you probably understood 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 with a little theory.
INSERT is a T-SQL instruction that is designed to add data to a table, i.e. creating new entries. This statement 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 part of the data that needs to be inserted:
Simplified syntax
INSERT [table] ( list of columns...) VALUES ( list of values...) or SELECT sample request Or EXECUTE procedure
This is 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 can be omitted, in which case their order will be determined based on the actual order of the columns in the table. However, you must remember this order when you specify values to insert or write a select query. Personally, I recommend that you still specify a list of columns in which you plan to add data.
It should also be remembered that in the list of columns and in the list of values, respectively, there must be so-called mandatory columns, these are those that cannot contain the NULL value. If they are not specified, 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 \u200b\u200bthat 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.
In order to add data to the table, we need the table itself, respectively, let's create it, and we will already try to add records to it.
Note! All examples will be executed 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 a price.
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 certain knowledge of the T-SQL language, so if something is not clear to you, I recommend that you familiarize yourself with the following materials:
First, let's try to add one record and immediately look at the result, i.e. Let's write a query for a selection.
INSERT INTO TestTable(ProductName, Price) VALUES("Computer", 100) GO SELECT * FROM TestTable
You can 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 \u200b\u200bthat we want to insert.
After the INSERT statement, I wrote a SELECT statement and separated them with a GO command.
Now let's imagine that we need to add some lines. We will write the following query for this.
INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100), ("Keyboard", 20), ("Monitor", 50) GO SELECT * FROM TestTable
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 a query.
INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id >
In this example, we wrote a SELECT query that returns data from the TestTable table, but not all, but only those with an identifier greater than 2. And the result was inserted into the same TestTable.
As an example of how you can add records to a table without specifying a list of columns, let's write another insert query that does the same thing as the query above, only it does 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 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 notice, I didn’t specify the Id column in all the examples, but we have it, there was no error, since this column has the IDENTITY property, it automatically generates identifiers, so inserting data into such a column simply won’t work.
Now let's insert 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 query, we specify 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 insert ( even if column list is not specified).
INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable
I hope this material helped you understand the instructions. INSERT INTO, and I have everything for now!
Last update: 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)
At the beginning there is an INSERT INTO statement, then in brackets you can specify a list of columns, separated by commas, into which data should be added, and at the end after the word VALUES, in brackets, the values to be added for the columns are listed.
For example, let's say the following database was created earlier:
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 a successful run in SQL Server Management Studio, the message box should show "1 row(s) affected":
Note 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 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 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 in which 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 have a DEFAULT attribute.
We can also add multiple 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 be used for the column 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 attribute that defines a default value, 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 a DEFAULT attribute and at the same time do not allow null values.
