T-SQL INSERT Code Examples

Various code examples for using Transact-SQL (T-SQL) to insert data into a database.

Insert 1 Row

This example inserts one row of data into the Artists table.

Specify the Column Names

You can also explicitly state the column names:

Insert Data that's in a Different Order to the Columns

Explicitly stating the column names is useful for when the source data is not in the same order as the columns in the table:

This example is exactly the same as the previous example, except I've swapped the column names around. Both examples will work (as long as the table has columns that match the column names provided).

Insert Multiple Rows

Multiple rows are separated by a comma:

Override the `IDENTITY` Property

You can set IDENTITY_INSERT to ON to override any value provided by an identity column.

In this example, explicit values are entered into the ArtistId column. This column is an identity column, so I need to enable IDENTITY_INSERT first before attempting to do this, otherwise an error would be thrown. If I chose not to provide explicit values for that column, values would be automatically generated due to the fact that it's an identity column.

Note that if the identity column has a primary key constraint, it will reject any values that already exist in the column (the primary key prevents duplicate values from being entered).

Copy Data Between Tables

You can copy data from one table to another like this:

This example copies data from the Artists table and inserts it into the Artists_Archive table.

Note that this example assumes that the schemas of both tables are identical. If the column names are different, then you'll need to explicitly list out each column name (like in the following examples).

Table has an Identity Column?

If the destination table has an identity column, you may receive an error explaining that you can't insert into an identity column without enabling IDENTITY_INSERT.

If you get this error, you'll either need to omit that column (by explicitly stating which columns to copy), or if you need to include that column, enable IDENTITY_INSERT.

Omit the Identity Column

This example copies only two columns from the source table.

Enable `IDENTITY_INSERT`

If you need to insert data into an identity column, and you need each value to match the source table, you'll need to override the IDENTITY property by enabling IDENTITY_INSERT. Like this:

In this example, ArtistId is the identity column. The Artists_Archive.ArtistId column will have its values populated from the source table (not by the IDENTITY property).

As in the previous example, I disable IDENTITY_INSERT on this table once I'm done inserting the data.

Copy Data to a New Table

You can use SELECT ... INTO to copy data into a newly created table. That is, a new table is created to hold the data.

It goes like this:

The Artists_Archive table didn't previously exist. Running this statement creates it, and inserts data from the Artists table.

You can also modify the query to limit the data copied. Here's an example of using a WHERE clause so that only older artists are copied to the new table:

Copy Data from Multiple Tables

You can insert data from multiple tables into a single table by expanding your query to select data from multiple tables. This example copies data from two tables and creates a new table (called JazzAlbums) to store that data.