The INSERT statement adds new rows into a table. The following variations of the INSERT statement are most commonly used:
INSERT...VALUES Insert a list of data values into an existing table.
INSERT...SELECT Insert the result of an inline query into an existing table.
INSERT...EXECUTE Insert the results of a stored procedure into a new table.
INSERT TOP Insert a specified number, or percent of rows from a query using the TOP clause.
SELECT...INTO Use this statement to create a new table based on query results. Although this is not technically an INSERT statement, we will learn about the SELECT...INTO statement, as it often is confused with the SELECT statement.
You can use the flowchart in Figure 1 to select the most appropriate INSERT statement syntax.
Using the INSERT Statement with the VALUES Clause
Let’s examine the basic syntax of the INSERT...VALUES statement. The following elements are required for a properly constructed INSERT statement.
INSERT...VALUES Statement—Core Elements
INSERT [INTO] table_or_view [(comma separated list of column names)]
VALUES ({data_value | DEFAULT | NULL | Scalar Expression}, ...n), [...n]
In this statement, the INSERT keyword is required. The INTO keyword is optional, and it makes no difference whether it is specified or not. The INTO keyword is considered a “noise” word.
The table_or_view
is the name of the table or view into which you wish to insert the
data. As well as tables and views, you can also use a table-valued
variable. If you are using a view for your INSERT
statement, it must be updateable. Updateable views are based upon a
single table. You could insert data into a multitable view, as long as
you specify columns from only one base table as the column_list.
The column_list is the list of columns from the table or view into which you will be inserting values. You can completely omit the column_list
if you want, but SQL Server will assume that you will provide values
for all columns in the table, and in the order they are defined in the
table. Using the column list, you can provide values for a subset of
columns only. The columns you do not include in the list will receive
their default values. You can specify columns in a different order as
they are defined in the table, as long as the order of the values you
are inserting matches the order of the columns you are inserting into.
VALUES is a keyword that specifies the list of literal values to insert. The VALUES clause essentially creates an in-memory set of rows out of the listed values. You can use a single VALUES clause to insert up to 1,000 rows of values. Within the actual list of values you can specify the following:
Data_value
A literal data value of the type corresponding to a column type; for
example, a varchar or an integer value. The value must be of the same
data type as the column you are inserting into, or be a data type that
can be implicitly converted to the column’s data type.
DEFAULT
Tells the INSERT statement to insert the default value for that column.
If the column does not have a default associated with it, a null value
is inserted.
NULL Tells the INSERT statement to explicitly insert a NULL value into the column, regardless of the default.
Scalar_Expression
A single-valued variable, subquery, function, or another expression.
Results of the expression will be inserted. The resultant value must be
of the same data type as the column you are inserting into, or be a data
type that can be implicitly converted to the column’s data type.
Sometimes, the INSERT
statement needs to be modified to accommodate certain types of columns,
like IDENTITY columns and GUID columns that may use NEWID as the
default value. When these types of columns are configured with default
values, it is recommended that the INSERT
statement simply omits the columns, and does not specify a value for
these columns. We will discuss these special cases later in this
chapter. Under no circumstances can you insert values into computed
columns. If you attempt to insert values into a computed column, an
error will be raised.
In Example 1 we will insert a single row into the Sales.Currency table using the VALUES
clause. This example does not specify column names, so all the columns
in the Currency table are used, and column values are specified by the VALUES clause in the same order as they are defined in the table.
Example 1. Inserting a Single Row Using VALUES Clause
USE AdventureWorks
GO
INSERT Sales.Currency
VALUES ('ZZZ', 'Unspecified Currency', GETDATE())
GO
|
Note that this
statement would have failed, if the Currency table had more than three
columns. This is why it is a best practice to specify the column list
explicitly. This is useful maintainability. You are able to add an extra
column to the table, without affecting your stored procedures that use
the INSERT statement.
Exam Warning
Always choose to specify column names, as this helps avoid issues in the future when the column structure changes.
In Example 2
we will insert three rows of values into the HumanResources.
Departments table, specifying the list of columns. The Departments table
contains DepartmentID, Name, GroupName, and ModifiedDate columns, in
that order. The example INSERT statement omits the DepartmentID column, so the next IDENTITY
value for the table is used. The example also lists GroupName, Name,
and ModifiedDate columns. The order of columns is different from the
column order in the table, but the INSERT statement will still work, because we have explicitly listed the columns, and specified column values in the correct order.
Example 2. Inserting Multiple Rows Using VALUES Clause
INSERT HumanResources.Department (GroupName, Name, ModifiedDate)
VALUES
('Executive General and Administration', 'Database Administration',
GETDATE()),
('Inventory Management', 'Stock Control', GETDATE()),
('Executive General and Administration', 'Legal', GETDATE())
|
Exam Warning
When dealing with an
IDENTITY column, it is best to omit it from the column list altogether.
Omitting the column will ensure that the next IDENTITY value is inserted into the column. This helps avoid the error “Cannot insert explicit value for identity column in table ‘Department’ when IDENTITY_INSERT is set to OFF.
