Basic SQL INSERT Statement with Examples
How do I load or insert data into a SQL database table? How do I use a SQL INSERT statement?
In SQL, the INSERT statement is one method used to insert data to SQL tables. There are various techniques for loading data with an INSERT statement including inserting a single row, multiple rows, inserting query results and inserting stored procedure results. I will also show how to create and load a table from a SELECT statement. Be sure to review the Next Steps at the bottom of this tutorial to learn more about topics mentioned.
The SQL INSERT statement is a DML command (Data Manipulation Language) and has several variations. In the examples that follow I will show how to insert 1 row or many rows of data at a time. The basic INSERT statement follows this example with the INSERT key word, the schema and table name, the table columns list and the values clause that has a value for each column in the column list. The order of the columns and value must correspond. Though, the order of the columns does not have to match the order of the columns in the table.
INSERT schema.TableName (Col1, Col2, Col3, etc.) VALUES (value1, value2, value3, etc );
In this tutorial, I will give an example of a basic insert along with several other examples using other methods to insert data to a SQL table.
First - Set Up Test Table for Insert Statement
To start the examples, first create a test table to use for the INSERT examples with the SQL statements below. Take note that the test table dbo.CustomerMonthlySales has a RecordID column that has an integer datatype and an Identity property. This is commonly used when creating tables to ensure the table has an automatic generated new unique ID for each row. As you will see in the examples, this column is ignored in the INSERT statement. Also, note that when writing an INSERT statement all non-nullable columns that do not have a default constraint defined must be included in the column list and values list of the INSERT statement. In the test table there is also a Nullable column, SalesAverage, that in the initial examples is ignored, but added in the later examples. Because this is a NULLable column it is not required when doing an INSERT. See the Next Steps section at the bottom for links to learn more about Identity Property and Default constraints.
Use AdventureWorks; GO --Create Test Table CREATE Table dbo.CustomerMonthlySales ( RecordID INT IDENTITY(1,1) NOT NULL, CustomerID INT NOT NULL, SalesMonth DATE NOT NULL, SalesTotal MONEY NOT NULL, SalesAverage MONEY NULL --For later use );
Example 1 – Basic Insert Statement to Insert Rows
This example shows a basic INSERT statement. The first INSERT statement includes only the required non-Nullable columns. The second INSERT statement does include the optional nullable SalesAverage column. Also note the RecordID column is also excluded as Identity Property columns are automatically handled by SQL when a new row is created. Each column in the VALUES logic is comma separated. Here is the basic syntax:
--1) Insert Statement INSERT dbo.CustomerMonthlySales (CustomerID, SalesMonth, SalesTotal) VALUES (11000, '2011-07-01', 3956.00); -- Column Values --1a) Insert including NULLABLE Column INSERT dbo.CustomerMonthlySales (CustomerID, SalesMonth, SalesTotal, SalesAverage) VALUES (11000, '2011-07-01', 100.00, 100.00); -- Column Values --Show Results SELECT * FROM dbo.CustomerMonthlySales; -- Existing Table
The results show the records inserted from the 2 INSERT statements. Note the RecordID is auto populated and the SalesAverage null for the first insert as the column allows NULL values and has no default constraint defined.
Example 2 – Insert Multiple Values with Table Value Constructor
This example is like the first example but shows that the VALUES clause can be repeated to insert multiple rows. This is a great way to insert multiple rows but has a limitation of 1000 rows. For more than 1000 rows break the inserts into multiple statements or use one of the other following examples.
--2) Insert Multiple rows INSERT dbo.CustomerMonthlySales (CustomerID, SalesMonth, SalesTotal) VALUES (11000,'2011-08-01',3350.00), (11000,'2011-09-01',2350.00), (11000,'2011-10-01',4150.00), (11000,'2011-11-01',4350.00); --up to 1000 rows for INSERT VALUES --Show Results SELECT * FROM dbo.CustomerMonthlySales;
Example 3 – SQL INSERT INTO from a Select Query
The following statement shows how to insert the results of a query into a table. This is another way to insert 1 or more rows depending on the query result set. This follows the same rules, excluding the RecordID Identity column and the optional SalesAverage column. The columns returned by the query match the order and datatype of the columns in the Insert columns list.
--3) Insert Select Query Results INSERT dbo.CustomerMonthlySales (CustomerID, SalesMonth, SalesTotal) SELECT CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) as SalesMonth, SUM(TotalDue) as SalesTotal FROM [Sales].[SalesOrderHeader] -- Source Table GROUP BY CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) ORDER BY CustomerID, SalesMonth; --Show Results SELECT * FROM dbo.CustomerMonthlySales;
Example 4 – Insert Into a New Table
This example loads the results of a query directly to a New Table. This is a common example often used in T-SQL scripts and Stored Procedures. In this case the new table is a Temp table denoted by the #TableName. SQL automatically creates the table based on the column names and data types from the Query results. All users can create temp tables. However, to create permanent tables you must have special rights in the database. If you have rights in the database to create tables, then you can use an example like this to create a permanent table. Just replace the INTO #TempTable with your Desired table name like: INTO dbo.MyTable.
--4) Insert from Select to Create a table. SELECT CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) as [SalesMonth], SUM(TotalDue) as [SalesTotal] INTO #TempTable FROM [Sales].[SalesOrderHeader] GROUP BY CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) ORDER BY CustomerID, SalesMonth; --Show Results SELECT * FROM #TempTable; GO DROP Table #TempTable;
Example 5 –Insert From a Stored Procedure Call
In this example I show how to insert data to a table from a Stored Procedure result. This can be a handy method for loading tables especially if there is a lot of logic required to produce the results. This example starts with the creation of a Stored Procedure that has code similar to example 3 but in this case, I will include the optional SalesAverage column. I execute the Stored Proc to test it and see the results prior to doing the insert from the Stored Procedure results. In the final query shows the records inserted and I limit the results by only returning rows where the SalesTotal does not match the SalesAverage.
--5) Insert from a Store Procedure Call --Create Stored Proc that calculates the Sum and Average TotalDue CREATE PROCEDURE dbo.usp_CustomerMonthlySales_Get AS BEGIN SELECT CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) as SalesMonth, SUM(TotalDue) as SalesTotal, AVG(TotalDue) as SalesAverage FROM [Sales].[SalesOrderHeader] GROUP BY CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) ORDER BY CustomerID, SalesMonth; END; GO --Exec the Stored Proc to See the Results EXEC dbo.usp_CustomerMonthlySales_Get; GO --Insert the Results of the Stored Proc directly to the Table INSERT dbo.CustomerMonthlySales (CustomerID, SalesMonth, SalesTotal, SalesAverage) EXEC dbo.usp_CustomerMonthlySales_Get; --Show Inserted Records the SalesAverage does not match the SalesTotal SELECT * FROM dbo.CustomerMonthlySales WHERE SalesTotal <> SalesAverage; GO
Note that the results of the Stored Procedure must match the columns in the column list.
I hope you learned about SQL INSERT statements. Please be sure to leave a comment if you found this helpful or if you have questions about this tip. Review the articles listed below to learn more about Identity Property, Default Constraints, Stored Procedures and SELECT INTO tables and temporary tables, touched on in this article.
- Check back to for upcoming articles on Updating and Deleting SQL data
- Learn How to Write a SQL SELECT Statement
- Learn more about Identity - INSERT INTO SQL Server table with IDENTITY column
- Learn about Default Constraints - Working with DEFAULT constraints in SQL Server
- Get more on SQL SELECT INTO Examples
- Read about SQL JOIN Types with Examples
- Read: Getting started with Stored Procedures in SQL Server
- Read: INSERT INTO SQL Server Command
- Read: INSERT INTO SELECT
- Read: SQL Server Primary Key and Foreign Key
About the author
View all my tips
Article Last Updated: 2022-06-17