How To Insert Date In Sql

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

Problem

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

Solution

In SQL, the INSERT statement is i method used to insert data to SQL tables. In that location 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 tabular array from a SELECT statement. Be certain to review the Next Steps at the lesser of this tutorial to learn more than about topics mentioned.

The SQL INSERT statement is a DML command (Information Manipulation Language) and has several variations. In the examples that follow I will show how to insert 1 row or many rows of information at a time. The bones INSERT statement follows this example with the INSERT key discussion, the schema and table name, the table columns list and the values clause that has a value for each cavalcade in the column list. The gild of the columns and value must correspond. Though, the gild of the columns does not accept to match the club of the columns in the tabular array.

INSERT schema.TableName (Col1, Col2, Col3, etc.) VALUES (value1, value2, value3, etc );

In this tutorial, I will requite an instance of a bones insert forth with several other examples using other methods to insert data to a SQL table.

Start - Gear up Test Table for Insert Statement

To commencement the examples, first create a test tabular array to utilize 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 tabular array has an automated generated new unique ID for each row. As you lot will run into in the examples, this column is ignored in the INSERT statement. Likewise, note that when writing an INSERT statement all non-nullable columns that do non have a default constraint defined must be included in the column list and values list of the INSERT argument. In the test table there is too a Nullable column, SalesAverage, that in the initial examples is ignored, only added in the later examples. Because this is a NULLable column information technology is not required when doing an INSERT. Come across the Side by side Steps section at the bottom for links to learn more nearly Identity Property and Default constraints.

Use AdventureWorks; GO  --Create Test Table CREATE Tabular array dbo.CustomerMonthlySales (    RecordID     INT IDENTITY(1,one) NOT Cipher,    CustomerID   INT Not NULL,    SalesMonth   DATE Not NULL,    SalesTotal   MONEY Not Cipher,    SalesAverage MONEY Naught  --For after use );
Get

Instance 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 cavalcade is besides excluded every bit Identity Belongings columns are automatically handled past 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 Cavalcade 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 Tabular array
Get

The results evidence the records inserted from the ii INSERT statements. Note the RecordID is auto populated and the SalesAverage null for the get-go insert as the cavalcade allows NULL values and has no default constraint defined.

query results

Instance ii – Insert Multiple Values with Table Value Constructor

This example is similar the first example simply shows that the VALUES clause tin be repeated to insert multiple rows. This is a great way to insert multiple rows just has a limitation of 1000 rows. For more than than 1000 rows pause the inserts into multiple statements or utilise ane of the other post-obit 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   --Evidence Results SELECT * FROM dbo.CustomerMonthlySales;
GO

Instance three – SQL INSERT INTO from a Select Query

The following argument shows how to insert the results of a query into a table. This is another fashion to insert 1 or more rows depending on the query result prepare. This follows the same rules, excluding the RecordID Identity cavalcade and the optional SalesAverage column. The columns returned by the query match the order and datatype of the columns in the Insert columns listing.

--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 Tabular array GROUP BY CustomerID, DateAdd(Month,DateDiff(MONTH,0,OrderDate),0) ORDER BY CustomerID, SalesMonth;   --Show Results SELECT * FROM dbo.CustomerMonthlySales;
Get

Example iv – Insert Into a New Table

This example loads the results of a query directly to a New Table. This is a common instance often used in T-SQL scripts and Stored Procedures. In this example the new table is a Temp table denoted past 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. Nevertheless, to create permanent tables you must have special rights in the database. If you have rights in the database to create tables, then y'all tin can use an example similar this to create a permanent table. Just supplant the INTO #TempTable with your Desired table proper 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(Calendar month,0,OrderDate),0) ORDER Past CustomerID, SalesMonth;   --Show Results SELECT * FROM #TempTable; GO  DROP Tabular array #TempTable;
Go

Example 5 –Insert From a Stored Procedure Call

In this case I show how to insert data to a table from a Stored Procedure result. This can be a handy method for loading tables specially 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 instance 3 merely in this instance, I will include the optional SalesAverage column. I execute the Stored Proc to test it and run into 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 past merely returning rows where the SalesTotal does non friction match the SalesAverage.

--5) Insert from a Store Process Call   --Create Stored Proc that calculates the Sum and Average TotalDue CREATE PROCEDURE dbo.usp_CustomerMonthlySales_Get As Brainstorm    SELECT CustomerID,       DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0) as SalesMonth,       SUM(TotalDue) as SalesTotal,       AVG(TotalDue) every bit SalesAverage    FROM [Sales].[SalesOrderHeader]    Grouping By CustomerID, DateAdd(MONTH,DateDiff(MONTH,0,OrderDate),0)    Guild BY CustomerID, SalesMonth; END; GO  --Exec the Stored Proc to See the Results EXEC dbo.usp_CustomerMonthlySales_Get; Get   --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 friction match the SalesTotal SELECT *  FROM dbo.CustomerMonthlySales WHERE SalesTotal <> SalesAverage; Go

Annotation that the results of the Stored Procedure must friction match the columns in the column list.

Wrap Up

I hope you learned about SQL INSERT statements. Delight be certain to get out a annotate if you found this helpful or if you have questions about this tip. Review the articles listed below to acquire more about Identity Holding, 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 Cardinal and Foreign Key

Related Manufactures

Popular Manufactures

About the author

MSSQLTips author Jim Evans Jim Evans is an It Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.