SQL INSERT into Table with Code Examples

By: Jim Evans | Updated: 2022-06-17 | Comments | Related: > TSQL

Problem

How do I load or insert data into a SQL database table? How do I use a SQL INSERT statement?

Solution

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
);
GO

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
GO

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;
GO

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;
GO

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;
GO

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.

Wrap Up

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.

Next Steps

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

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-06-17

Basic SQL INSERT Statement with Examples