SQL INSERT INTO Statement

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.

Similar Articles