SQL INSERT INTO Statement

Introduction

 
In this chapter, we’ll learn how to INSERT INTO SQL Statement works and different options used with the INSERT statement. 
 

Insert INTO Statement in SQL

 
The SQL INSERT INTO statement is used to insert a single record or multiple records into a database table using SQL.
 
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 the INSERT INTO statement. The INSERT INTO is followed by a table name with its columns and followed by VALUES and the values 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 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.
 
If a value is not specified for this column, the default value is used.
 
Column_3 is defined with the row version 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.     
  15. INSERT INTO StudentDetail(StudentID,StudentName,StudentAddress,StudentCiy)         
  16.     VALUES ('Amar','flatNo108 Preet Vihar','NewDelhi');        
  17. GO        
  18. SELECT StudentID,StudentName,StudentAddress,StudentCiy    
  19. FROM StudentDetail       
  20. 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 an 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 ;                     

The 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 the 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 the next chapter, we will learn the basics of the SQL UPDATE statement and how it works with SQL Statements.
Author
Naresh Beniwal
Editor 7.1k 1.5m
Next » SQL UPDATE Statement Tutorial

©2024 C# Corner.