SQL INSERT INTO Statement

In this article, you will learn how to use an INSERT INTO statement in SQL

Insert and exclusive lock

 
The SQL INSERT INTO statement is used to insert a single record or multiple records into a database table using SQL. In this tutorial, we’ll learn how INSERT INTO SQL works and different options used with the INSERT statement.
 
SQL INSERT INTO statement adds data of one or more records to a database. Either all the rows can be inserted, or a subset may be chosen using a condition. 
 
Here is the syntax of INSERT INTO statement. The INSERT INTO is followed by a table name with its columns and followed by VALUES and the calues of the respective columns.  
 
Syntax
    1. INSERT INTO table    
    2. (column1, column2, ... )    
    3. VALUES    
    4. (expression1, expression2, ... ),    
    5. (expression1, expression2, ... ),    
    6. ...;    

    Insert a single row in a table

     
    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 values for all columns are supplied and are 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
    1. Insert into EmployeeDetails values('Ravi','c-321 Sector55 Noida','Noida','Noida',9978654332)              
    Example  
     
     
     

    Insert multiple rows in a table

     
    The INSERT INTO statement can be used to insert multiple rows by grouping the statement. The following SQL inserts three rows into the EmployeeDetail table in the sample database. Because values for all columns are supplied and are 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
    1. Insert into EmployeeDetails values('Ravi','c-321 Sector55 Noida','Noida','Noida',9978654332,'Rohan',    
    2. 'D-211 NoidaSector44','Noida','GauthamBudhNagar',9865454398,    
    3. 'Rohit','C-321 Sector 64 Faridabad','Faridabad','OldFaridabad',9892124359)   
      Example
       

      Insert data that is not in the same order as the table columns

       
      This example uses a column list to explicitly specify the values that are inserted into each column.
       
      The column order in the EmployeeDetail table in the Sample database is EmpName, EmpAddress, EmpCity; however, the columns are not listed in that order in column_list
       
      Syntax
      1. Insert into EmployeeDetails values('PriyanK','Indrapuram,','Ghaziabd''Ghaziabad',9055345544, GETDATE());      

      Insert data into a table with columns that have 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.
       
      If a value is not specified for this column, the default value is used.
       
      Column_3 is defined with the rowversion data type, which automatically generates 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 INSERT statements insert rows that contain values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause. 
       
      Syntax
      1. CREATE TABLE Employee      
      2. (      
      3.     column_1 AS 'Computed column ' + column_2,       
      4.     column_2 varchar(30)       
      5.         CONSTRAINT default_name DEFAULT ('my column default'),      
      6.     column_3 rowversion,      
      7.     column_4 varchar(40) NULL      
      8. );      
      9. GO      
      10. INSERT INTO Employee (column_4)       
      11.     VALUES ('Explicit value');      
      12. INSERT INTO Employee (column_2, column_4)       
      13.     VALUES ('Explicit value''Explicit value');      
      14. INSERT INTO Employee (column_2)       
      15.     VALUES ('Explicit value');      
      16. INSERT INTO Employee DEFAULT VALUES;       
      17. GO      
      18. SELECT column_1, column_2, column_3, column_4      
      19. FROM Employee;      
      20. GO     

      Insert data into a table with an identity column

       
      INSERT data into an identity column. The first two INSERT statements allow identity values to be generated for new rows.
       
      The third 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
      1. CREATE TABLE StudentDetail (     
      2. StudentID  int IDENTITY(1,1),    
      3. StudentName varchar(50),    
      4. StudentAddress varchar(50),    
      5. StudentCiy varchar(50)    
      6. )    
      7. Go    
      8.      
      9. INSERT StudentDetail VALUES ('Ravin','101 Noida Sector 63','Noida');        
      10. INSERT StudentDetail  VALUES ('Rohit','103 Rahul Vihar','NewDelhi');        
      11. GO        
      12. SET IDENTITY_INSERT StudnetDetail ON;        
      13. GO        
      14. INSERT INTO StudentDetail(StudentID,StudentName,StudentAddress,StudentCiy)         
      15.     VALUES ('Amar','flatNo108 Preet Vihar','NewDelhi');        
      16. GO        
      17. SELECT StudentID,StudentName,StudentAddress,StudentCiy    
      18. FROM StudentDetail       
      19. GO    
        Example 
         
         

        Insert data into a uniqueidentifier column by using NEWID() 

         
        The uniquidentifier column uses the NEWID() function to obtain a GUID for column_2. Unlike for identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier data type, as shown by the second INSERT statement. 
         
        Syntax
        1. CREATE TABLE  CollegeDetail         
        2. (        
        3.     CollegeId int IDENTITY,         
        4.     CollegeName uniqueidentifier,        
        5. );        
        6. GO        
        7. INSERT INTO dbo.CollegeDetail(CollegeName)         
        8.     VALUES (NEWID());        
        9. INSERT INTO CollegeDetail DEFAULT VALUES;         
        10. GO        
        11. SELECT CollegeId, CollegeName        
        12. FROM CollegeDetail  
        Example
         
         

        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 table by using the INSERT...SELECT or 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 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 INSERT statement uses the EXECUTE clause to call a stored procedure that contains the SELECT statement, and the third INSERT uses the EXECUTE clause to reference the SELECT statement as a literal string.
         
        Syntax
        1. CREATE TABLE NewEmployeeDetail      
        2. (    
        3. Id int Primary kei Identity(1,1),    
        4. DataSource   varchar(20) NOT NULL,      
        5.   BusinessEntityID   varchar(11) NOT NULL,      
        6.   LastName     varchar(40) NOT NULL,      
        7.   SalesDollars money NOT NULL      
        8. );      
        9. GO      
        10. CREATE PROCEDURE dbo.uspGetEmployeeSales       
        11. AS       
        12.     SET NOCOUNT ON;      
        13.     SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,       
        14.         sp.SalesYTD       
        15.     FROM Sales.SalesPerson AS sp        
        16.     INNER JOIN Person.Person AS c      
        17.         ON sp.BusinessEntityID = c.BusinessEntityID      
        18.     WHERE sp.BusinessEntityID LIKE '2%'      
        19.     ORDER BY sp.BusinessEntityID, c.LastName;      
        20. GO      
        21. --INSERT...SELECT example      
        22. INSERT INTO dbo.EmployeeSales      
        23.     SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD       
        24.     FROM Sales.SalesPerson AS sp      
        25.     INNER JOIN Person.Person AS c      
        26.         ON sp.BusinessEntityID = c.BusinessEntityID      
        27.     WHERE sp.BusinessEntityID LIKE '2%'      
        28.     ORDER BY sp.BusinessEntityID, c.LastName;      
        29. GO      
        30. --INSERT...EXECUTE procedure example      
        31. INSERT INTO dbo.EmployeeSales       
        32. EXECUTE dbo.uspGetEmployeeSales;      
        33. GO      
        34. --INSERT...EXECUTE('string') example      
        35. INSERT INTO dbo.EmployeeSales       
        36. EXECUTE       
        37. ('      
        38. SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName,       
        39.     sp.SalesYTD       
        40.     FROM Sales.SalesPerson AS sp       
        41.     INNER JOIN Person.Person AS c      
        42.         ON sp.BusinessEntityID = c.BusinessEntityID      
        43.     WHERE sp.BusinessEntityID LIKE ''2%''      
        44.     ORDER BY sp.BusinessEntityID, c.LastName      
        45. ');      
        46. GO      
        47. --Show results.      
        48. SELECT DataSource,BusinessEntityID,LastName,SalesDollars      
        49. FROM dbo.EmployeeSales;     
           

        Insert the common table expression to define data 

         
        The INSERT statement creates the NewEmployee table in the Sampledatabase. A common table expression (EmployeeDetails) defines the rows from one or more tables to be inserted into the NewEmployee table. The INSERT statement references the columns in the common table expression.
         
        Syntax
        1. CREATE TABLE  NewEmployee      
        2. (      
        3.     EmployeeID int NOT NULL,      
        4.     LastName nvarchar(50) NOT NULL,      
        5.     FirstName nvarchar(50) NOT NULL,      
        6.     PhoneNumber Phone NULL,      
        7.     AddressLine1 nvarchar(60) NOT NULL,      
        8.     City nvarchar(30) NOT NULL,      
        9.     State nchar(3) NOT NULL,       
        10.     PostalCode nvarchar(15) NOT NULL,      
        11.     CurrentFlag Flag      
        12. );      
        13. GO      
        14. WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,       
        15.                    Address, City, StateProvince,       
        16.                    PostalCode, CurrentFlag)      
        17. AS (SELECT       
        18.        e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,      
        19.        a.AddressLine1, a.City, sp.StateProvinceCode,       
        20.        a.PostalCode, e.CurrentFlag      
        21.     FROM HumanResources.Employee e      
        22.         INNER JOIN Person.BusinessEntityAddress AS bea      
        23.         ON e.BusinessEntityID = bea.BusinessEntityID      
        24.         INNER JOIN Person.Address AS a      
        25.         ON bea.AddressID = a.AddressID      
        26.         INNER JOIN Person.PersonPhone AS pp      
        27.         ON e.BusinessEntityID = pp.BusinessEntityID      
        28.         INNER JOIN Person.StateProvince AS sp      
        29.         ON a.StateProvinceID = sp.StateProvinceID      
        30.         INNER JOIN Person.Person as c      
        31.         ON e.BusinessEntityID = c.BusinessEntityID      
        32.     )      
        33. INSERT INTO NewEmployee       
        34.     SELECT EmpID, LastName, FirstName, Phone,       
        35.            Address, City, StateProvince, PostalCode, CurrentFlag      
        36.     FROM EmployeeTemp;      
        37. GO     
           

        INSERT TOP to limit the data inserted from the source table

         
        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 INSERT statement chooses any 5 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
        1. CREATE TABLE  Employee     
        2. ( EmployeeID   nvarchar(11) NOT NULL,      
        3.   LastName     nvarchar(20) NOT NULL,      
        4.   FirstName    nvarchar(20) NOT NULL,      
        5.   YearlySales  money NOT NULL      
        6.  );      
        7. GO      
        8. INSERT TOP(5)INTO  employeeDetails  
        9.     OUTPUT inserted.EmployeeID, inserted.FirstName,     
        10.         inserted.LastName, inserted.YearlySales      
        11.     SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD       
        12.     FROM Employee     AS sp      
        13.     INNER JOIN Person.Person AS c      
        14.         ON sp.BusinessEntityID = c.BusinessEntityID      
        15.     WHERE sp.SalesYTD > 250000.00      
        16.     ORDER BY sp.SalesYTD DESC;   
           

        Insert data by specifying a view in 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 INSERT statement must match the column order of the view. 
         
        Syntax
        1. CREATE TABLE Students (StudentID int, StudentName varchar(30));      
        2. GO      
        3. CREATE VIEW Student AS       
        4. SELECT  StudentID, StudentName       
        5. FROM Students     
        6. GO      
        7. INSERT INTO  Student      
        8.     VALUES (1,'Ravi');      
        9. GO      
        10. SELECT StudentID, StudentName  from Students      
        11. GO      
        12. SELECT  StudentID,StudentName      
        13. FROM  Student     
        14. GO      
        Example 
         

        Insert data into a table variable 

         
        The INSERT data specifies a table variable as the target object in the Sample database.
         
        Syntax
        1. -- Create the table variable.      
        2. DECLARE @MyTableVar table(      
        3.     LocationID int NOT NULL,      
        4.     CostRate smallmoney NOT NULL,      
        5.     NewCostRate AS CostRate * 1.5,      
        6.     ModifiedDate datetime);      
        7.       
        8. -- Insert values into the table variable.      
        9. INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)      
        10.     SELECT LocationID, CostRate, GETDATE()     
        11.     FROM Production.Location      
        12.     WHERE CostRate > 0;      
        13.       
        14. -- View the table variable result set.      
        15. SELECT * FROM @MyTableVar;      
        16. GO     

        Insert Rows into a Remote Table

         
        This section demonstrates how to insert rows into a remote target table by using a linked server or a rowset function to reference the remote table.
         

        Insert data into a remote table by using a linked server

         
        This INSERTs rows into a remote table. The example begins by creating a link to the remote data source by 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
        1. USE master;      
        2. GO      
        3. -- Create a link to the remote data source.       
        4. -- Specify a valid server name for @datasrc as 'server_name'     
        5. -- or 'server_nameinstance_name'.      
        6.       
        7. EXEC sp_addlinkedserver @server = N'MyLinkServer',      
        8.     @srvproduct = N' ',      
        9.     @provider = N'SQLNCLI',       
        10.     @datasrc = N'server_name',      
        11.     @catalog = N'Sample';      
        12. GO     

        Insert data into a remote table by using the OPENQUERY function 

         
        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
        1. INSERT OPENQUERY (MyLinkServer,     
        2.     'SELECT EmployeeName, EmployeeAddress  
        3.      FROM Sample.EmployeeDetails')      
        4. VALUES ('Ravi''Noida');      
        5. GO     

        Inserts data into a remote table by using the OPENDATASOURCE function

         
        Syntax
        1. INSERT INTO OPENDATASOURCE('SQLNCLI',      
        2.     'Data Source= <server_name>; Integrated Security=SSPI')      
        3.     EmployeeDetail (EmpName, EmpAddress)      
        4.     VALUES ('Rahul''Noida63');      
        5. GO                       

        Insert into an external table created using PolyBase

         
        The INSERT can be used to 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 INSERT INTO to export data from a local SQL Server table to an external data source. The 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 specified file format.
         
        Syntax 
        1.  CREATE EXTERNAL TABLE [dbo].[CustomerDetails] (      
        2.         [FirstName] char(25) NOT NULL,       
        3.         [LastName] char(25) NOT NULL,       
        4.         [YearlyIncome] float NULL,       
        5.         [MaritalStatus] char(1) NOT NULL      
        6. )      
        7. WITH (      
        8.         LOCATION='/old_data/2009/customerdata.tbl',      
        9.         DATA_SOURCE = HadoopHDP2,      
        10.         FILE_FORMAT = TextFileFormat,      
        11.         REJECT_TYPE = VALUE,      
        12.         REJECT_VALUE = 0      
        13. );      
        14.       
        15. -- Export data: Move old data to Hadoop while keeping     
        16. -- it query-able via external table.      
        17.     
        18. INSERT INTO CustomerDetails      
        19. SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2      
        20. ON (T1.CustomerKey = T2.CustomerKey)      
        21. WHERE T2.YearMeasured = 2009 and T2.Speed > 40;   

        Insert data using the OPENROWSET function with BULK to bulk load data into a table

         
        The 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 by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).
         
        Syntax
        1. INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)      
        2. SELECT b.Name, b.GroupName       
        3. FROM OPENROWSET (      
        4.     BULK 'C:SQLFilesDepartmentData.txt',      
        5.     FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',      
        6.     ROWS_PER_BATCH = 15000)AS b ;                     

        he TABLOCK hint to specify a locking method

         
        The following specifies that an exclusive (X) lock is taken on the EmployeeDetail and is held until the end of the INSERT statement. 
         
        Syntax
        1. INSERT INTO Production.Location WITH (XLOCK)      
        2. (Name, CostRate, Availability)      
        3. VALUES ( N'Final Inventory', 15.00, 80.00);  

        Insert data using SELECT option

         
        The INSERT data shows how to insert multiple rows of data using an INSERT statement with a SELECT option. The first INSERT statement uses a SELECT statement directly to retrieve data from the source table and then to store the result set in the EmployeeTitles table.
         
        Syntax 
        1. CREATE TABLE EmployeeData      
        2. ( EmployeeKey   INT NOT NULL,      
        3.   LastName     varchar(40) NOT NULL,      
        4.   Title      varchar(50) NOT NULL      
        5. );      
        6. INSERT INTO EmployeeData      
        7.     SELECT EmployeeKey, LastName, Title       
        8.     FROM EmployeeData     
        9.     WHERE EndDate IS NULL;  

        Summary

         
        In this tutorial, we learned the basics of the SQL INSERT INTO statement.