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
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:
You can set
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
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:
Artists_Archive table didn't previously exist. Running this statement creates it, and inserts data from the
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.