T-SQL Basics. DML. SQL INSERT INTO SELECT, Sql syntax insert into statement
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, then you probably realized 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, as usual, with a little theory.
INSERT statement in T-SQL
INSERT is a T-SQL instruction that is designed to add data to a table, i.e. creating new records. This instruction can be used both to add a single row to a table and to insert data in bulk. The INSERT statement requires permission to insert data ( INSERT) to the target table.
There are several ways to use the INSERT statement on the piece of data that needs to be inserted:
- Listing specific values to insert;
- Specifying a data set as a SELECT query;
- Specifying a data set in the form of a procedure call that returns tabular data.
Simplified syntax
INSERT [table] ( list of columns...) VALUES ( list of values...) Or SELECT sample request Or EXECUTE procedure
- INSERT INTO is a command to add data to a table;
- Table is the name of the target table into which you want to insert new records;
- The column list is a list of column names of the table into which the data will be inserted, separated by commas;
- VALUES is a table value constructor with which we specify the values that we will insert into the table;
- The list of values is the values that will be inserted, separated by commas. They are listed in the order that the columns appear in the column list;
- SELECT is a query to select data to insert into a table. The result set that the query returns must match the list of columns;
- EXECUTE is a procedure call to obtain data for insertion into a table. The result set that the stored procedure returns must match the list of columns.
This is roughly 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 does not need to be written, in which case their order will be determined based on the actual order of the columns in the table. You must remember this order when you specify values to insert or write a query to select. Personally, I recommend that you still indicate a list of columns into which you plan to add data.
You should also remember that the list of columns and the list of values, respectively, must contain so-called required columns; these are those that cannot contain the NULL value. If you do not specify them, 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 that 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.
Initial data
In order to add data to the table, we need the table itself, so let's create it and try to add records to it.
Note! All examples will be run 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 prices.
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 having some knowledge of T-SQL language, so if something is not clear to you, I recommend that you familiarize yourself with the following materials:
Example 1 – Adding a new record to a table using the table value constructor
First let's try adding one record and immediately look at the result, i.e. Let's write a request for a sample.
INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100) GO SELECT * FROM TestTable
You 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 keyword VALUES and in brackets also, in the same order, separated by commas, we wrote the values that we want to insert.
After the INSERT statement, I wrote a SELECT statement and separated them with a GO statement.
Now let's imagine that we need to add a few lines. We will write the following request for this.
INSERT INTO TestTable(ProductName, Price) VALUES ("Computer", 100), ("Keyboard", 20), ("Monitor", 50) GO SELECT * FROM TestTable
Example 2 - Adding new rows to a table using a SELECT query
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 the request.
INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id >
IN in this example we wrote a SELECT query that returns data from the TestTable table, but not all of it, but only those with an ID greater than 2. And the result was inserted into the same TestTable table.
As an example of how you can add records to a table without specifying a list of columns, let's write another data insertion query that will do exactly the same thing as the query above, only it will 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 table 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 noticed, in all the examples I did not specify the Id column, but we have it, no errors occurred, since this column has the IDENTITY property, it automatically generates identifiers, so inserting data into such a column simply cannot be done.
Example 3 - Adding new records to a table using a stored procedure
Now let's insert the 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 request we indicate 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 be inserted ( even if the 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 that’s all I have for now!
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:
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 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 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 request is made to select numbers and names of departments located in Moscow, and the resulting result set is loaded into 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.
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 stored query. |
|
value1, value2 |
Values to be added to specific fields new entry. 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. If a query is given, the database kernel Microsoft Access adds records to all 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 an INSERT INTO statement along with a VALUES clause for each additional entry that you want to create.
WITH 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 INSERT INTO and INSERT SELECT statements are used to insert new rows into a table. There are two ways to use instructions:
- Values Only: The first method involves specifying only the data values to be inserted without the column names.
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3,...); table_name: table name. value1, value2,.. : values of the first column, second column,... for the 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: table name. 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 INTO SELECT, 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 | 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 for which no values are specified 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 statement is similar to using INSERT INTO. The difference is that the SELECT statement is used to select data from another table. Below are the different ways to use INSERT INTO SELECT:
- Insert all table columns: You can copy all the data in a table and paste it into another table.
Syntax:
INSERT INTO first_table SELECT * FROM second_table; first_table: name of the first table. second_table: 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.
- Inserting 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: name of the first table. second_table: name of the second table. col_names1: comma(,) separated column names for table 1. col_names2: comma(,) separated column names for table 2.
We used the SELECT statement to copy data only from the selected columns of the second table and the 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 to later paste into another table by using a WHERE clause with a SELECT clause. In this case, you need to use the appropriate condition in 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 the data from the LateralStudent table into the Student table. After using 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. The remaining columns of the Student table will be set to null . After using 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 to insert:
Result :
This query will select only the first row from the LateralStudent table to insert into the Student table. After using INSERT SELECT, the table will look like this.