Last Updated:

SQL INSERT INTO SELECT Statement | SQL Database

INSERT statement in T-SQL

INSERT is a T-SQL statement that is used to add data to a table, i.e. create new records. You can use this statement to add a single row to a table or to bulk insert data. To execute an INSERT statement, you must have the Insert Data (INSERT) permission to the target table.

There are several ways to use the INSERT statement in the part of the data that you want to insert:

  • Enumerate specific values to insert;
  • Specify a dataset as a SELECT query;
  • Specify a dataset as a procedure call that returns tabular data.

Note! For beginners, I recommend watching my video course on T-SQL.

Simplified syntax

 

INSERT [INTO] [table] (list of columns, ...) VALUES (list of values, ...) or SELECT query on a sample or EXECUTE procedure

Where is

    • INSERT INTO is a command to add data to a table;
    • The table is the name of the target table into which you want to insert new records;
 
  • A column list is a list of column names in a table into which comma-separated data will be inserted;
  • 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 in which the columns in the column list are listed;
  • SELECT is a query to select data to insert into a table. The result dataset that the query returns must match the list of columns;
  • EXECUTE is a procedure call to retrieve data to insert into a table. The result dataset 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 can not be written, in which case their order will be determined based on the actual order of the columns in the table. Keep this order in mind when you specify values to insert or write a selection query. Personally, I recommend that you still specify the list of columns to 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 required columns, these are those that cannot contain a 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 correspond to the data type of the column into which this value will be inserted, or, at least, supported an implicit transformation. But I advise you to control the data type (format) of values, both in the list of values and in the SELECT query.

Enough theory, let's move on to practice.

Error handling

For an INSERT statement, you can implement error handling by specifying a statement in the TRY... CATCH.

If an INSERT statement violates a constraint or rule, or if it contains a value that is incompatible with the data type of the column, the statement fails and displays an error message.

If an INSERT statement loads multiple rows by using a SELECT or EXECUTE statement, any rule or constraint violations that occur due to the values being loaded cause the statement to stop executing, and none of the rows will be loaded.

If an arithmetic error (overflow, division by zero, or domain error) occurs while executing an INSERT statement, the Database Engine handles these errors as if the SET ARITHABORT parameter had been set to ON. The package stops running and an error message is displayed. During expression evaluation, when set ARITHABORT and SET ANSI_WARNINGS set to OFF, if an overflow, divide by zero, or scope error occurs in an INSERT, DELETE, or UPDATE statement, SQL Server inserts or updates a null value. If the target column is not empty, the insertion or update is not performed and the user receives an error.

Compatibility

 

If an INSTEAD OF trigger is defined in INSERT operations on a table or view, the trigger is executed instead of an INSERT statement. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

Restrictions

If you do not specify all the values for all columns when you insert values into remote tables, you must specify the columns into which the specified values are inserted.

When you use the TOP expression in an INSERT statement, the referenced strings are not ordered, and the ORDER BY clause cannot be explicitly specified in those statements. If you want to use the TOP clause to insert rows in meaningful chronological order, use the ORDER BY clause in the subquery statement. Cm. See the "Examples" section later in this topic.

 

INSERT queries that use SELECT with ORDER BY to populate rows guarantee a way to calculate identifier values, but not the order in which rows are inserted.

In Parallel Data Warehouse, the ORDER BY clause is not valid in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT statements, embedded functions, derived tables, subqueries, and generalized table expressions unless you also specify TOP.

Logging mode

The INSERT statement is always fully logged, except when you use the OPENROWSET function with the BULK keyword or execute an INSERT INTO SELECT FROM statement. Minimal logging is possible for these operations. For more information, see the "Best Practices for Bulk Loading Data" section of this topic.

Security

When connecting to a linked server, the sending server specifies a login and password to connect to the receiving server on its behalf. For this connection to work, you must create a login mapping between linked servers by calling the stored procedure sp_addlinkedsrvlogin.

When using the OPENROWSET(BULK...) function, it is important to understand how SQL Server handles impersonation. For more information, see the "Security Considerations" chapter in Bulk Import Data By Using bulk INSERT or OPENROWSET(BULK...) (SQL Server).

Permissions

Requires INSERT permission on the target table.

INSERT permissions are granted by default to members of the sysadmin fixed server role, members of the fixed database roles db_owner and db_datawriter, and to the owner of the table. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can delegate permissions to other users.

To execute an INSERT statement with the BULK parameter of the OPENROWSET function, you must be a member of the sysadmin or bulkadmin fixed server role.

Difference between inserting and updating

This section can also be named, or alternate section headings:

  • Difference between INSERT and UPDATE

The INSERT INTO SQL statement only inserts new records (rows, rows) into an existing table, not inserts new values into the cells of existing records (rows, rows). Inserting new values into the cells of existing records (rows, rows) in SQL is handled by the UPDATE update statement.

Types of insertion

 

The INSERT INTO SQL statement supports inserting data in two ways: inserting data by values and inserting by selection.

Insert by values

Inserting by values provides the following INSERT syntax ... VALUES. Insertions in this way can be made in two forms: full and abbreviated. In the full form, in addition to the table name itself, the columns of the table into which the data is inserted are also indicated, in the abbreviated form, the columns are not indicated. For each of the forms, multiple insertion is available, in which rows with values (parentheses) are listed separated by commas.

Insert on demand

On-demand insertion provides the following syntax: INSERT ... SELECT. That is, first the data is pulled out of one table, and then they are inserted into another table. At the same time, the following conditions must be met:

  • The number of values in the row (the number of columns) obtained by the query must match the number of columns when inserted.
  • The data types must match.
  • column names don't matter.

Source data

In order to add data to the table, we need the table itself, let's create it, and we will try to add records to it.

Note All of the examples will run in Microsoft SQL Server 2016 Express.

 
CREATE TABLE TestTable( [Id] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NOT NULL )

Our test table will contain a list of goods with price.

Also in the examples, we'll use a procedure that returns a table value to add data to a table, so let's create one too.

CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END

For example, it will return data from the newly created TestTable.

Example 1 – Adding a New Record to a Table Using the Table Value Constructor

First, let's try to add one record and immediately look at the result, i.e. write a query for a sample.

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 specified the VALUES keyword and in parentheses also, in the same order, separated by commas, wrote the values that we want to insert.

After the INSERT statement, I wrote a SELECT statement and separated them with the GO command.

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

 

In this case, we added three entries, i.e. three lines. After VALUES, the values of each new line are indicated in parentheses, we separated them with commas.

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 the table, for example, based on a query for a selection, i.e. SELECT. To do this, instead of VALUES, we only need to specify a query.

INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable

 

In this example, we wrote a SELECT query that returns data from the TestTable, not all, but only those with an ID 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 data insert query that will do 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, 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, I did not specify the Id column in all the examples, and we have it, there was no error, since this column with the IDENTITY property, it automatically generates identifiers, so it simply will not be possible to insert data into such a column.

Example 3 – Adding New Records to a Table Using a Stored Procedure

For 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 query, we specify a call to the procedure. But as you can imagine, the order and number of columns returned by the procedure must exactly match the list of columns to insert (even if the column list is not specified).

INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable

 

Sampling conditions

 

condition
WHERE
logical OR
OR
logical AND
AND
sorting
ORDER BY
LIMIT sampling
limit
selection from the
INTERVAL BETWEEN
specified IN values

T search by
like template
changing
AS field names
unique DISTINCT values

negation
NOT
value is not NULL
IS NOT NULL
value is NULL
IS NULL

Grouping

  • grouping
    GROUP BY
  • condition  group by
    HAVING

String addition

  • concat string
    addition
  • addition with CONCAT_WS separator
  • addition of cells
    GROUP_CONCAT

Multiple tables

  • union tables joining
  • linking JOIN tables
  • левый join
    LEFT JOIN
  • правый join
    RIGHT JOIN
  • interior join
    INNER JOIN
  • copy to another
    SELECT INTO table

String length

  • LENGTH string
    length

Edit strings

  • left left cut
  • right right cut
  • taking the
    SUBSTRING substring
  • taking the MID substring
  • taking a substring
    SUBSTRING_INDEX
  • replace and find and replace
  • addition of the line on the left
    LPAD
  • row addition on the right
    RPAD
  • REVERSE STRING
    FLIP
  • repeating the repeat line
    REPEAT

1. Add whole rows

As the name implies, the INSERT statement is used to insert (add) rows to a database table. Adding can be done in several ways:

  • — add one full line
  • — add part of the line
  • — add the results of the query.

So, to add a new row to the table, we need to specify the name of the table, list the column names and specify the value for each column using the INSERT INTO table_name construct (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’)
SQL Table

You can also change the order in which column names are specified, but you must also change the order of the values in the VALUES parameter.

2. Add part of the rows

In the previous example, when we used the INSERT statement, we explicitly marked the column names of the table. Using this syntax, we can skip some columns. This means that you enter a value for some columns but do not suggest them for others. For example:

 

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

SQL Table 2

In this example, we did not specify a value for the two columns Address and Country. You can exclude some columns from the INSERT INTO statement if it allows you to define a table. In this case, one of the following conditions must be met: this column is defined as a nullable value (the absence of any value) or the specified default value in the table definition. This means that if no value is specified, the default value will be used. If you omit a column in a table that does not allow null values to appear in its rows and does not have a value defined for use by default, the DBMS will throw an error message and this row will not be added.

3. Add selected data

In the previous examples, we inserted data into tables, manually writing them in the query. However, the INSERT INTO statement allows us to automate this process if we want to insert data from another table. To do this, in SQL there is such a structure as INSERT INTO ... SELECT ... . This design allows you to simultaneously select data from one table, and insert them into another. Suppose we have another table of 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 their names), but the data is different. To do this, we can prescribe the following request:

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU

Note that the internal key values are not duplicated (the ID field), otherwise an error will occur. The SELECT statement can also include WHERE clauses to filter the data. It should also be noted that the DBMS does not pay attention to the names of columns that are contained in the SELECT statement, only the order of their location is important for it. Therefore, the data in the first specified column that was selected because of SELECT will in any case be populated into the first column of the Sellers table specified after the INSERT INTO statement, regardless of the field name.

4. Copy data from one table to another

Often, when working with databases, there is a need to create copies of any tables, for the purpose of backup or modification. To make a full copy of a table, SQL provides a separate SELECT INTO statement. For example, we need to create a copy of the Sellers table, we will need to specify the query as follows:

SELECT * INTO Sellers_new FROM Sellers

 

Unlike the previous INSERT INTO design... SELECT ... , when data is added to an existing table, the SELECT ... INTO ... FROM ... Copies the data to a new table. You can also say that the first construct imports data, and the second one exports. When using the SELECT ... INTO ... FROM ... consider the following:

  • - you can use any of the clauses in the select statement, such as group by and having
  • - To add data from multiple tables, you can use joining
  • - Data can only be added to one table, regardless of how many tables they were taken from.

Insert data only in specified columns

In addition, you can insert data only into specific columns.

The following SQL statement inserts a new record, but only inserts data into the CustomerName, City, and Country columns (the CustomerID will be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

The selection from the Customers table will now look like this:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
89White Clover MarketsKarl Jablonski305 — 14th Ave. S. Suite 3BSeattle98128USA
90Wilman KalaMatti KarttunenKeskuskatu 45Helsinki21240Finland
91WolskiZbyszekul. Filtrowa 68Walla01-012Poland
92Cardinalnullnull StavangernullNorway

SQL INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and pastes it into another table.

  • INSERT INTO SELECT requires that the data types in the source and destination tables match
  • Existing records in the destination table remain unchanged

Синтаксис INSERT INTO SELECT

To copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy only some columns from one table to another table:

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

Demo database

In this tutorial, we will use the well-known Northwind database example.

Below is a selection from the "Customers" table:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico

And the choice from the "Suppliers" table:

SupplierIDSupplierNameContactNameAddressCityPostal CodeCountry
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonaEC1 4SDUK
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA
3Grandma Kelly’s HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA

Примеры SQL INSERT INTO SELECT

The following SQL statement copies "Suppliers" to "Customers" (columns that are not populated with data will contain NULL):

Example

 

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers; Попробуйте сами »

The following SQL statement copies "Suppliers" to "Customers" (fill in all columns):

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; Попробуйте сами »

The following SQL statement copies only German vendors to the Customers section:

Example

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country=’Germany’; Попробуйте сами