what sql clause would you use with the insert into command to add records in a table?

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples, and utilise cases.

In my earlier article SQL SELECT INTO Argument, we explored the post-obit tasks.

• Create a SQL table on the wing while inserting records with appropriate information types
• Use SQL SELECT INTO to insert records in a item FileGroup
• Nosotros cannot use it to insert information in an existing table

The INSERT INTO SELECT statement

We want to insert records equally regular database activity. We tin insert data direct using customer tools such as SSMS, Azure Data Studio or directly from an awarding. In SQL, we use the SQL INSERT INTO statement to insert records.

The syntax of the INSERT INTO

One time we insert data into the table, we can utilise the following syntax for our SQL INSERT INTO statement.

INSERT INTO table_name ( Column1 , Column ii.... ) VALUES ( value1 , value2 , . . . ) ;

If we accept specified all column values as per table cavalcade orders, nosotros practise not need to specify column names. We can directly insert records into the table.

INSERT INTO table_name VALUES ( value1 , value2 , . . . ) ;

Let us create a sample table and insert data into it.

CREATE TABLE Employees ( ID INT , Proper noun VARCHAR ( twenty ) ) ;

We tin can insert data using the following queries. Both queries are valid for data insertion.

Insert into Employees ( ID , Name ) values ( 1 , 'raj' ) Insert into Employees values ( ii , 'raj' )

Nosotros cannot insert information without specifying cavalcade names if there is a mismatch between information insertion and the order of column values is different. We can get the following mistake message.

• Msg 213, Level 16, State ane, Line vi

  Column name or number of supplied values does non friction match tabular array definition.

• Msg 245, Level 16, State ane, Line 6

  Conversion failed when converting the varchar value 'raj' to data type int.

In this example, nosotros'll utilize the SQL INSERT INTO statement with supplying values directly in a argument. Suppose we want to insert data from some other table. We can withal use the SQL INSERT INTO statement with a select statement. Let's explore this in the side by side department.

INSERT INTO SELECT Statement Syntax

We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement.

INSERT INTO table1 ( col1 , col2 , col3 , …) SELECT col1 , col2 , col3 , … FROM table2

This query performs the post-obit tasks:

• It start Selects records from a table ( Select statement)
• Adjacent, information technology inserts into a table specified with INSERT INTO

• Note: The Cavalcade structure should match between the column returned by SELECT statement and destination tabular array.

INSERT INTO SELECT examples

Case 1: insert data from all columns of source table to destination table

We have the post-obit records in an existing Employee tabular array.

Allow us create another table Customers with the post-obit query.

CREATE Tabular array Customers ( ID INT , Name VARCHAR ( 20 ) ) ;

We want to insert all records from the Employees tabular array to the Customers table. We can use the SQL INSERT INTO SELECT argument to do this.

INSERT INTO Customers SELECT * FROM Employees ;

Information technology inserts all records into the Customers tabular array. We can verify the records in Customers table are similar to the Employees table.

In this example, we inserted records for all columns to the Customers tabular array.

Example 2: Insert rows from source to destination tabular array by specifying column names

Let'southward drop the existing Customers table before nosotros move forward. Now, nosotros want to create a table with one additional IDENTITY column. IDENTITY column automatically inserts identity values in a table. We as well added a City column that allows NULL values

CREATE TABLE Customers ( ID INT IDENTITY ( 1 , 1 ) , Emp_ID INT , Proper noun VARCHAR ( xx ) , Urban center VARCHAR ( 20 ) Nothing , ) ;

We cannot utilize the INSERT INTO SELECT statement similar to the to a higher place example. If we try to run this code, nosotros get an error message.

INSERT INTO Customers SELECT * FROM Employees ;

In this instance, we demand to specify the column name with INSERT INTO statement.

INSERT INTO Customers ( Emp_ID , Name ) SELECT * FROM Employees ;

In the Customers table, we have an additional column with allows NULL values. Let'southward run a Select on Customers table. In the following screenshot, we can see NULL values in the City column.

Suppose you accept a different column in the source table. Y'all can still insert records into the destination table with specifying column names in the INSERT INTO SELECT statement. We should have an appropriate data type to insert data. You cannot insert a varchar column information into an INT cavalcade.

Add a new column in Employees table using ALTER Table statement.

ALTER Table Employees Add Country varchar ( fifty ) ;

Update the tabular array records with country value India.

Update Employees set Country = 'India'

At present, rerun the INSERT INTO SELECT statement. You can notice that nosotros are using SELECT * instead of specifying cavalcade names.

INSERT INTO Customers ( Emp_ID , Name ) SELECT * FROM Employees ;

We become the following error message. This error comes considering of the column mismatch betwixt the source table and destination tabular array.

We can map the column between the source and destination table using the following query.

INSERT INTO Customers ( Emp_ID , Proper name ) SELECT ID , Name FROM Employees ;

Example 3: Insert top rows using the INSERT INTO SELECT statement

Suppose we want to insert Elevation N rows from the source table to the destination table. We tin utilise Top clause in the INSERT INTO SELECT statement. In the following query, it inserts the summit ane row from the Employees table to the Customers table.

INSERT TOP ( 1 ) INTO Customers ( Emp_ID , Proper name ) SELECT ID , Proper noun FROM Employees ;

Example 4: Insert using both columns and defined values in the SQL INSERT INTO SELECT Statement

In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to go records from the source tabular array and insert it into the destination table.

Nosotros can combine both columns and defined values in the SQL INSERT INTO SELECT statement.

We take the following columns in the Customers and Employees table. Previously, we did non insert any values for the City column. We practise not take the required values in the Employee table also. We need to specify an explicit value for the City column.

In the post-obit query, we specified a value for the City column while the rest of the values nosotros inserted from the Employees tabular array.

INSERT Acme ( 1 ) INTO Customers ( Emp_ID , Name , Urban center ) SELECT ID , Proper name , 'Delhi' FROM Employees ;

In the following query, we tin can see it inserts 1 row (due to Peak (1) clause) along with value for the City column.

Example 5: INSERT INTO SELECT statement with Bring together clause to become information from multiple tables

Nosotros can use a JOIN clause to go data from multiple tables. These tables are joined with conditions specified with the ON clause. Suppose nosotros want to go data from multiple tables and insert into a table.

In this example, I am using AdventureWorks2017 database. Commencement, create a new table with advisable data types.

CREATE TABLE [ HumanResources ] . [ EmployeeData ] ( [ FirstName ] [ dbo ] . [ Name ] NOT NULL , [ MiddleName ] [ dbo ] . [ Name ] NULL , [ LastName ] [ dbo ] . [ Name ] NOT NULL , [ Suffix ] [ nvarchar ] ( ten ) Nada , [ JobTitle ] [ nvarchar ] ( fifty ) NOT NULL , [ PhoneNumber ] [ dbo ] . [ Telephone ] NULL , [ PhoneNumberType ] [ dbo ] . [ Proper noun ] Nothing , [ EmailAddress ] [ nvarchar ] ( fifty ) NULL , [ City ] [ nvarchar ] ( 30 ) Not NULL , [ StateProvinceName ] [ dbo ] . [ Name ] Not Zip , [ PostalCode ] [ nvarchar ] ( 15 ) Not Null , [ CountryRegionName ] [ dbo ] . [ Name ] Non Aught ) ON [ PRIMARY ] GO

This table should contain records from the output of a multiple table join query. Execute the following query to insert data into HumanResources.EmployeeData tabular array.

1 2 3 4 v 6 7 8 9 10 xi 12 13 14 15 16 17 xviii xix 20 21 22 23

INSERT INTO HumanResources . EmployeeData SELECT p . [ FirstName ] , p . [ MiddleName ] , p . [ LastName ] , p . [ Suffix ] , due east . [ JobTitle ] , pp . [ PhoneNumber ] , pnt . [ Name ] AS [ PhoneNumberType ] , ea . [ EmailAddress ] , a . [ City ] , sp . [ Proper noun ] AS [ StateProvinceName ] , a . [ PostalCode ] , cr . [ Name ] Equally [ CountryRegionName ] FROM [ HumanResources ] . [ Employee ] e INNER JOIN [ Person ] . [ Person ] p ON p . [ BusinessEntityID ] = east . [ BusinessEntityID ] INNER JOIN [ Person ] . [ BusinessEntityAddress ] bea ON bea . [ BusinessEntityID ] = east . [ BusinessEntityID ] INNER Bring together [ Person ] . [ Address ] a ON a . [ AddressID ] = bea . [ AddressID ] INNER JOIN [ Person ] . [ StateProvince ] sp ON sp . [ StateProvinceID ] = a . [ StateProvinceID ] INNER Join [ Person ] . [ CountryRegion ] cr ON cr . [ CountryRegionCode ] = sp . [ CountryRegionCode ] LEFT OUTER Join [ Person ] . [ PersonPhone ] pp ON pp . BusinessEntityID = p . [ BusinessEntityID ] LEFT OUTER JOIN [ Person ] . [ PhoneNumberType ] pnt ON pp . [ PhoneNumberTypeID ] = pnt . [ PhoneNumberTypeID ] LEFT OUTER Bring together [ Person ] . [ EmailAddress ] ea ON p . [ BusinessEntityID ] = ea . [ BusinessEntityID ] ; GO

Example 6: INSERT INTO SELECT statement with mutual table expression

We utilize Mutual Tabular array Expressions (CTE) to simplify complex bring together from multiple columns. In the previous example, we used JOINS in a Select statement for inserting information into a SQL table. In this office, we will rewrite the query with CTE.

In a CTE, we can divide code into 2 parts.

1. We define CTE past a WITH clause before SELECT, INSERT, UPDATE, DELETE argument
2. One time we define CTE, nosotros can accept reference the CTE similar to a relational SQL tabular array

Execute the following code to insert data using a CTE.

ane 2 3 4 5 6 7 eight nine ten 11 12 13 14 15 sixteen 17 xviii 19 xx 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

WITH EmployeeData_Temp ( [ FirstName ] , [ MiddleName ] , [ LastName ] , [ Suffix ] , [ JobTitle ] , [ PhoneNumber ] , [ PhoneNumberType ] , [ EmailAddress ] , [ City ] , [ StateProvinceName ] , [ PostalCode ] , [ CountryRegionName ] ) AS ( SELECT p . [ FirstName ] , p . [ MiddleName ] , p . [ LastName ] , p . [ Suffix ] , e . [ JobTitle ] , pp . [ PhoneNumber ] , pnt . [ Name ] AS [ PhoneNumberType ] , ea . [ EmailAddress ] , a . [ Metropolis ] , sp . [ Proper noun ] As [ StateProvinceName ] , a . [ PostalCode ] , cr . [ Proper name ] Equally [ CountryRegionName ] FROM [ HumanResources ] . [ Employee ] due east INNER JOIN [ Person ] . [ Person ] p ON p . [ BusinessEntityID ] = e . [ BusinessEntityID ] INNER JOIN [ Person ] . [ BusinessEntityAddress ] bea ON bea . [ BusinessEntityID ] = e . [ BusinessEntityID ] INNER JOIN [ Person ] . [ Accost ] a ON a . [ AddressID ] = bea . [ AddressID ] INNER JOIN [ Person ] . [ StateProvince ] sp ON sp . [ StateProvinceID ] = a . [ StateProvinceID ] INNER JOIN [ Person ] . [ CountryRegion ] cr ON cr . [ CountryRegionCode ] = sp . [ CountryRegionCode ] LEFT OUTER Bring together [ Person ] . [ PersonPhone ] pp ON pp . BusinessEntityID = p . [ BusinessEntityID ] LEFT OUTER JOIN [ Person ] . [ PhoneNumberType ] pnt ON pp . [ PhoneNumberTypeID ] = pnt . [ PhoneNumberTypeID ] LEFT OUTER JOIN [ Person ] . [ EmailAddress ] ea ON p . [ BusinessEntityID ] = ea . [ BusinessEntityID ] ) INSERT INTO HumanResources . EmployeeData SELECT * FROM EmployeeData_Temp ; GO

Example 7: INSERT INTO SELECT statement with a Table variable

We use Table variables similarly to a temporary table. We tin can declare them using the table data blazon. This table can exist used to perform activities in SQL Server where nosotros do not require a permanent table. You can divide the post-obit query into three parts.

1. Create a SQL Table variable with appropriate cavalcade data types. We need to apply information type Tabular array for table variable
2. Execute a INSERT INTO SELECT argument to insert data into a tabular array variable
3. View the table variable result ready

1 2 3 iv 5 6 7 viii ix 10 11 12 13 14 15 sixteen 17 18 19 20 21 22 23 24 25 26 27

DECLARE @ TableVar tabular array ( [ JobTitle ] [ nvarchar ] ( fifty ) Not Nix , [ BirthDate ] [ date ] NOT Zilch , [ MaritalStatus ] [ nchar ] ( 1 ) NOT NULL , [ Gender ] [ nchar ] ( 1 ) Non Goose egg , [ HireDate ] [ appointment ] NOT NULL , [ SalariedFlag ] [ dbo ] . [ Flag ] Not Nada , [ VacationHours ] [ smallint ] Not Zip , [ SickLeaveHours ] [ smallint ] Non Nothing ) -- Insert values into the table variable. INSERT INTO @ TableVar SELECT [ JobTitle ] , [ BirthDate ] , [ MaritalStatus ] , [ Gender ] , [ HireDate ] , [ SalariedFlag ] , [ VacationHours ] , [ SickLeaveHours ] FROM [ AdventureWorks2017 ] . [ HumanResources ] . [ Employee ] -- View the table variable consequence set. SELECT * FROM @ TableVar ; GO

Conclusion

In this article, we explore the use cases of the INSERT INTO SELECT statement. I hope you found this article helpful. Feel free to provide feedback in the comments below.

• Writer
• Recent Posts

Hi! I am Rajendra Gupta, Database Specialist and Builder, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over xiv years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest gratuitous online collections of manufactures on a single topic, with his fifty-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized equally the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions honour in 2020.

Personal Blog: https://world wide web.dbblogger.com
I am ever interested in new challenges so if y'all need consulting aid, attain me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

fieldsliffeccand.blogspot.com

Source: https://www.sqlshack.com/sql-insert-into-select-statement-overview-and-examples/

Share this post