Introduction
SQL INSERT INTO statement inserts single or multiple records into a database table using SQL. In this tutorial, we'll learn how to INSERT INTO SQL works and the different options used with the SQL INSERT statement.
SQL INSERT INTO Syntax
SQL INSERT INTO statement adds data of one or more records to a database. All the rows can be inserted, or a subset may be chosen using a condition.
Here is the syntax of the SQL INSERT INTO statement. The SQL INSERT INTO is followed by a table name with its columns and followed by VALUES and the calues of the respective columns.
Syntax
INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;
How to insert a single row in a table using SQL using INSERT INTO?
The following example inserts one row into the EmployeeDetails table in the sample database.
The columns in this table are EmpId, EmpName, EmpAddress, and EmpCity. Because all columns' values are supplied and listed in the same order as the columns in the table, the column names do not have to be specified in the column list.
Syntax
Insert into EmployeeDetails values('Deepak',' E-101 120','Ghaziabad','UP',9832123321)
Example
![]()
How to insert multiple rows in a table in SQL using INSERT INTO?
The SQL INSERT INTO statement can insert multiple rows by grouping the statement. The following SQL inserts three rows into the EmployeeDetail table in the sample database. Because all columns' values are supplied and listed in the same order as the columns in the table, the column names do not have to be specified in the column list.
Syntax
Insert into EmployeeDetails values('Ravi','c-321 sector 55 Noida','Noida','Up',9978654332)
Insert into EmployeeDetails values('Rohan','D-211 sector 28 Faridabad,''Hariyana',9865454398)
Insert into EmployeeDetails values('Rohit','C-111 sector 64','Merut','Uttar Pradesh',9892124359)
Example
![]()
SQL Insert data that is not in the same order as the table columns
This example uses a column list to specify the values inserted into each column explicitly.
The column order in the EmployeeDetail table in the Sample database is EmpName, EmpAddress, and EmpCity; however, the columns are not listed in that order in column_list.
Syntax
Insert into EmployeeDetails values('PriyanK',' Indrapuram,', 'Ghaziabad', 'Ghaziabad',9055345544, GETDATE());
SQL Insert data into a table with columns that have a default value
This statement shows inserting rows into a table with columns that automatically generate a value or have a default value. Column_1 is a computed column that automatically generates value by concatenating a string with the value inserted into column_2. Column_2 is defined with a default constraint.
The default value is used if a value is not specified for this column.
Column_3 is defined with the row version data type, automatically generating a unique, incrementing binary number.
Column_4 does not automatically generate a value. When a value for this column is not specified, NULL is inserted. The SQL INSERT statements insert rows that contain values for some of the columns but not all. No columns are specified in the last SQL INSERT statement, and only the default values are inserted using the DEFAULT VALUES clause.
Syntax
CREATE TABLE Employee
(
column_1 AS 'Computed column ' + column_2,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('my column default'),
column_3 rowversion,
column_4 varchar(40) NULL
);
GO
INSERT INTO Employee (column_4)
VALUES ('Explicit value');
INSERT INTO Employee (column_2, column_4)
VALUES ('Explicit value', 'Explicit value');
INSERT INTO Employee (column_2)
VALUES ('Explicit value');
INSERT INTO Employee DEFAULT VALUES;
GO
SELECT column_1, column_2, column_3, column_4
FROM Employee;
GO
SQL Insert data into a table with an identity column
SQL INSERT data into an identity column. The first two SQL INSERT statements allow identity values to be generated for new rows.
The third SQL INSERT statement overrides the IDENTITY property for the column with the SET IDENTITY_INSERT statement and inserts an explicit value into the identity column.
Syntax
CREATE TABLE StudentDetail (
StudentID int IDENTITY(1,1),
StudentName varchar(50),
StudentAddress varchar(50),
StudentCiy varchar(50)
)
Go
INSERT StudentDetail VALUES ('Ravin','101 Noida Sector 63','Noida');
INSERT StudentDetail VALUES ('Rohit','103 Rahul Vihar','NewDelhi');
GO
SET IDENTITY_INSERT StudnetDetail ON;
GO
INSERT INTO StudentDetail(StudentID,StudentName,StudentAddress,StudentCiy)
VALUES ('Amar','flatNo108 Preet Vihar','NewDelhi');
GO
SELECT StudentID,StudentName,StudentAddress,StudentCiy
FROM StudentDetail
GO
Example
![]()
SQL Insert data into a uniqueidentifier column by using NEWID()
The uniquidentifier column uses the NEWID() function to obtain a GUID for column_2. Unlike identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier data type, as shown by the second SQL INSERT statement.
Syntax
CREATE TABLE CollegeDetail
(
CollegeId int IDENTITY,
CollegeName uniqueidentifier,
);
GO
INSERT INTO dbo.CollegeDetail(CollegeName)
VALUES (NEWID());
INSERT INTO CollegeDetail DEFAULT VALUES;
GO
SELECT CollegeId, CollegeName
FROM CollegeDetail
Example
![]()
SQL Insert into with Select and Execute options to insert data from other tables
The following example shows how to insert data from one table into another using the SQL INSERT...SELECT or SQL INSERT...EXECUTE. Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list.
The first SQL Insert statement uses a Select statement to derive the data from the source tables (Employee, EmployeeDetails, and EmployeeDetail) in the Sample database and store the result set in the EmployeeDetails table. The second SQL INSERT statement uses the EXECUTE clause to call a stored procedure containing the SELECT statement. The third SQL INSERT uses the Execute clause to reference the Select statement as a literal string.
Syntax
CREATE TABLE NewEmployeeDetail
(
Id int Primary kei Identity(1,1),
DataSource varchar(20) NOT NULL,
BusinessEntityID varchar(11) NOT NULL,
LastName varchar(40) NOT NULL,
SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales
AS
SET NOCOUNT ON;
SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales
EXECUTE
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE ''2%''
ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
Insert the common table expression to define the data
The SQL INSERT statement creates the NewEmployee table in the Sample database. A common table expression (EmployeeDetails) defines the rows from one or more tables to be inserted into the NewEmployee table. The SQL Insert statement references the columns in the common table expression.
Syntax
CREATE TABLE NewEmployee
(
EmployeeID int NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL,
PhoneNumber Phone NULL,
AddressLine1 nvarchar(60) NOT NULL,
City nvarchar(30) NOT NULL,
State nchar(3) NOT NULL,
PostalCode nvarchar(15) NOT NULL,
CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,
Address, City, StateProvince,
PostalCode, CurrentFlag)
AS (SELECT
e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
a.AddressLine1, a.City, sp.StateProvinceCode,
a.PostalCode, e.CurrentFlag
FROM HumanResources.Employee e
INNER JOIN Person.BusinessEntityAddress AS bea
ON e.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
INNER JOIN Person.PersonPhone AS pp
ON e.BusinessEntityID = pp.BusinessEntityID
INNER JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Person.Person as c
ON e.BusinessEntityID = c.BusinessEntityID
)
INSERT INTO NewEmployee
SELECT EmpID, LastName, FirstName, Phone,
Address, City, StateProvince, PostalCode, CurrentFlag
FROM EmployeeTemp;
GO
SQL Insert Top to limit the data inserted from the source table
SQL INSERT creates the table Employee and inserts the name and year-to-date sales data for the top 5 random employees from the table EmployeeDetails.EmployeeId in the Sample database. The SQL INSERT statement chooses any five rows returned by the SELECT statement. The output clause displays the rows that are inserted into the EmployeeDetails table. Notice that the ORDER BY clause in the SELECT statement is not used to determine the top 5 employees.
Syntax
CREATE TABLE Employee
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
YearlySales money NOT NULL
);
GO
INSERT TOP(5)INTO employeeDetails
OUTPUT inserted.EmployeeID, inserted.FirstName,
inserted.LastName, inserted.YearlySales
SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Employee AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
SQL Insert data by specifying a view in SQL
SQL INSERT specifies a view name as the target object; however, the new row is inserted in the underlying base table. The order of the values in the SQL INSERT statement must match the column order of the view.
Syntax
CREATE TABLE Students (StudentID int, StudentName varchar(30));
GO
CREATE VIEW Student AS
SELECT StudentID, StudentName
FROM Students
GO
INSERT INTO Student
VALUES (1,'Ravi');
GO
SELECT StudentID, StudentName from Students
GO
SELECT StudentID,StudentName
FROM Student
GO
Example
![]()
SQL Insert data into a table variable
The SQL Insert data specifies a table variable as the target object in the Sample database.
Syntax
-- Create the table variable.
DECLARE @MyTableVar table(
LocationID int NOT NULL,
CostRate smallmoney NOT NULL,
NewCostRate AS CostRate * 1.5,
ModifiedDate datetime);
-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
SELECT LocationID, CostRate, GETDATE()
FROM Production.Location
WHERE CostRate > 0;
-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO
SQL Insert Rows into a Remote Table
This section demonstrates how to insert rows into a remote target table using a linked server or a rowset function to reference the remote table.
SQL Insert data into a remote table by using a linked server
This SQL Insert rows into a remote table. The example begins by creating a link to the remote data source using sp_addlinkedserver. The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form server.catalog.schema.object.
Syntax
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name'
-- or 'server_nameinstance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'Sample';
GO
SQL Insert data into a remote table by using the OPENQUERY function
SQL INSERT a row into a remote table by specifying the OPENQUERY rowset function.
The linked server name created in the previous example is used in this example.
Syntax
INSERT OPENQUERY (MyLinkServer,
'SELECT EmployeeName, EmployeeAddress
FROM Sample.EmployeeDetails')
VALUES ('Ravi', 'Noida');
GO
SQL Inserts data into a remote table by using the OPENDATASOURCE function
Syntax
INSERT INTO OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
EmployeeDetail (EmpName, EmpAddress)
VALUES ('Rahul', 'Noida63');
GO
SQL Insert into an external table created using PolyBase
The SQL INSERT can export data from SQL Server to Hadoop or Azure Storage. First, we create an external table that points to the destination file or directory.
Then, use SQL INSERT INTO to export data from a local SQL Server table to an external data source. The SQL INSERT INTO statement creates the destination file or directory if it does not exist, and the results of the SELECT statement are exported to the specified location in the prescribed file format.
Syntax
CREATE EXTERNAL TABLE [dbo].[CustomerDetails] (
[FirstName] char(25) NOT NULL,
[LastName] char(25) NOT NULL,
[YearlyIncome] float NULL,
[MaritalStatus] char(1) NOT NULL
)
WITH (
LOCATION='/old_data/2009/customerdata.tbl',
DATA_SOURCE = HadoopHDP2,
FILE_FORMAT = TextFileFormat,
REJECT_TYPE = VALUE,
REJECT_VALUE = 0
);
-- Export data: Move old data to Hadoop while keeping
-- it query-able via external table.
INSERT INTO CustomerDetails
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2
ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;
SQL Insert data using the OPENROWSET function with BULK to bulk load data into a table
The SQL INSERT statement inserts rows from a data file into a table by specifying the OPENROWSET function. The IGNORE_TRIGGERS table hint is specified for performance optimization. For more examples, see Import Bulk Data using BULK SQL INSERT or OPENROWSET(BULK...) (SQL Server).
Syntax
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName
FROM OPENROWSET (
BULK 'C:SQLFilesDepartmentData.txt',
FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',
ROWS_PER_BATCH = 15000)AS b ;
The TABLOCK hint specifies a locking method.
The following specifies that an exclusive (X) lock is taken on the EmployeeDetail and is held until the end of the SQL INSERT statement.
Syntax
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
SQL Insert data using the SELECT option
The SQL INSERT data shows how to insert multiple rows of data using an SQL INSERT statement with a SELECT option. The first SQL INSERT statement uses a SELECT statement directly to retrieve data from the source table and store the result set in the EmployeeTitles table.
Syntax
CREATE TABLE EmployeeData
( EmployeeKey INT NOT NULL,
LastName varchar(40) NOT NULL,
Title varchar(50) NOT NULL
);
INSERT INTO EmployeeData
SELECT EmployeeKey, LastName, Title
FROM EmployeeData
WHERE EndDate IS NULL;
Summary
INSERT INTO SQL statement can be used differently to insert data into tables. In this tutorial, we learned the SQL INSERT INTO statement basics and various use cases with SQL examples.