INSERT Statement

 

SQL Server INSERT statement

To insert single or multiple records into a table, use the SQL Server (Transact-SQL) INSERT statement. There are several methods for managing data to insert into SQL Server. In this article, we will look at how to create and insert data into both permanent and temporary tables. 

The following is an example of the most basic INSERT statement:

         INSERT INTO table_name (column_list)

ALUES (value_list);

 

SQL Server INSERT statement examples

Let’s create a new table named CUSTOMER for the demonstration:

CREATE TABLE [Sales].[dbo].[CUSTOMER](

       [CUSTOMER_ID] [int] PRIMARY KEY IDENTITY (1, 1),

       [FIRST_NAME] [varchar](50) NOT NULL,

       [LAST_NAME] [varchar](50) NOT NULL,

       [DATE_OF_BIRTH] [date] NULL,

       [CITY_NAME] [varchar](100) NULL

)

In this statement, we created a new table named CUSTOMER in the dbo schema. Because the CUSTOMER ID is an identity column, the SQL Server automatically populates its value when you insert a new row into the table.

1.    Basic INSERT example using VALUES keyword and an explicit column list

INSERT INTO dbo.CUSTOMER

   (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

    ('Jason', 'Martin', '2000-06-01', 'Minnehaha');

This SQL Server INSERT statement would result in one record being inserted into the CUSTOMER table.

You can also use this syntax to insert more than one record at a time.

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

    ('Lief','Johnson','2001-09-01','Clinton'),

    ('Bobby','Wilcox','2002-10-10','Hardin');

This INSERT example shows how to insert more than one record using the VALUES keyword. In this example, two records are inserted into the CUSTOMER table.

This would be equivalent to the following two INSERT statements:

 

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

('Lief','Johnson','2001-09-01','Clinton');

 

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

('Bobby','Wilcox','2002-10-10','Hardin');

 

2.    Using DEFAULT VALUES keyword and an explicit column list

In SQL Server, you can also insert a record into a table using the DEFAULT VALUES syntax.

 

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

DEFAULT VALUES;

This SQL Server INSERT statement would result in one record being inserted into the CUSTOMER table. This new record would be created with default values for the fields.

 

3.    Insert Data into SQL Server without an explicit column list

This leads us into the alternative way to insert data into an existing table, which is to do so without a column list. An insert of this form will look like this:

 

INSERT INTO dbo.CUSTOMER

SELECT 'Tracy', 'Trundle', '2000-06-01', 'Minnehaha';

4.    Using SELECT statement

You can also create more complicated SQL Server INSERT statements using SELECT statements.

INSERT INTO Contacts

(contact_id, last_name, FIRST_NAME)

SELECT CUSTOMER_ID, LAST_NAME, first_name

FROM CUSTOMER

WHERE CUSTOMER_ID <= 10;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

5.    Insert Data into SQL Server with SELECT INTO 

It is possible to create a new table object and SELECT data into it as a part of a single statement. This can be a convenient way to avoid the hassle of defining a table up-front and enumerating column names. Typically, this is used to populate temporary tables.

 

SELECT

   'Tracy' AS FIRST_NAME,

   'Trundle' AS LAST_NAME,

   '2000-06-01' AS DATE_OF_BIRTH,

   'Minnehaha' AS CITY_NAME

INTO #Contacts;

 

SELECT INTO can also be used for a generic SELECT statement with no additional definitions, such as this:

 

SELECT

  *

INTO dbo.CUSTOMER_Backup

FROM dbo.CUSTOMER;

 

In a very short TSQL statement, we created a new permanent table and inserted all of the contents of CUSTOMER into it. That when this syntax is used, the data types in the resulting table will match the source table as they are currently defined.

 

6.    Insert and return inserted values

To capture the inserted values, you use the OUTPUT clause. For example, the following statement inserts a new row into the CUSTOMER table and returns the inserted value of the CUSTOMER_ID column:

 

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME,LAST_NAME,DATE_OF_BIRTH,CITY_NAME)

OUTPUT inserted.CUSTOMER_ID

VALUES

    ('Jason','Martin','2000-06-01','Minnehaha');

 

The following is the output:

 

 

To capture inserted values from multiple columns, you specify the columns in the output as shown in the following statement:

 

INSERT INTO dbo.CUSTOMER

       (FIRST_NAME,LAST_NAME,DATE_OF_BIRTH,CITY_NAME)

OUTPUT inserted.CUSTOMER_ID,

              inserted.FIRST_NAME,

              inserted.LAST_NAME,

              inserted.DATE_OF_BIRTH,

              inserted.CITY_NAME

VALUES

    ('Jones','Smith','2003-09-12','Tucson');

The following is the output:

 

7.    Insert explicit values into the identity column

Typically, you don’t specify a value for the identity column because SQL Server will provide the value automatically. However, in some situations, you may want to insert a value into the identity column such as data migration.

 

INSERT INTO dbo.CUSTOMER

       (CUSTOMER_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

    (8,'Clara','Choulai','2001-09-01','Rockville');

SQL Server issued the following error:

Msg 544, Level 16, State 1, Line 102

Cannot insert explicit value for identity column in table 'CUSTOMER' when IDENTITY_INSERT is set to OFF.

To insert explicit value for the identity column, you must execute the following statement first:

         SET IDENTITY_INSERT table_name ON;

To switch the identity insert off, you use a similar statement:

         SET IDENTITY_INSERT table_name OFF;

Let’s execute the following statements to insert a value for the identity column in the CUSTOMER table:

 

SET IDENTITY_INSERT dbo.CUSTOMER ON;

 

INSERT INTO dbo.CUSTOMER

       (CUSTOMER_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CITY_NAME)

VALUES

    (8,'Clara','Choulai','2001-09-01','Rockville');

 

SET IDENTITY_INSERT dbo.CUSTOMER OFF;

In this example, first, we switched the identity insert on, then inserted a row with an explicit value for the identity column, and finally switched the identity insert off.

The following shows the data of the CUSTOMER table after the insertion:

SELECT * FROM dbo.CUSTOMER