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).

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.