To insert a default value for columns with default constraints, attached default objects, those that allow NULL values, or timestamp columns, use the DEFAULT keyword in place of an actual value. DEFAULT causes columns with associated default constraints to receive their default values during the INSERT. When DEFAULT is specified with a NULLable column that doesn’t otherwise have a default value, the column is set to NULL. Timestamp columns get the database’s next timestamp value.

To specify explicitly a NULL value for a column that allows NULLs, use the NULL keyword. If you specify NULL for a column that doesn’t allow NULLs (or DEFAULT for a NOT NULL column without a default), your INSERT will fail. Here’s an example that illustrates DEFAULT and NULL:

(Results abridged)

Note that, contrary to the Books Online, you’re not always required to supply a value for every column in the target column list (or every column in the table if the INSERT doesn’t have a column list). Identity columns may be safely omitted from any INSERT statement—even those with target column lists. This is true regardless of where the identity column appears in the table. Here’s an example:

(Results abridged)

Not only are identity columns optional, but you are not allowed to specify them unless the SET IDENTITY_INSERT option has been enabled for the table. SET IDENTITY_INSERT allows values to be specified for identity columns. It’s handiest when loading data into a table that has dependent foreign keys referencing its identity column.

Unlike timestamp columns and columns with defaults, you may not specify a default value for an identity column using the DEFAULT keyword. You can’t include a value of any type for an identity column unless SET IDENTITY_INSERT has been enabled. Here’s an example that features SET IDENTITY_INSERT:

Note the inclusion of a target column list—it’s required when you specify a value for an identity column.

The second form of the command allows default values to be specified for all columns at once. It looks like this:

Here’s a simple example:

Here, default values are specified for all the table’s columns at once. As with the first form, if you use DEFAULT VALUES with columns that do not have defaults of some type defined, your INSERT will fail. Note that a target column list is illegal with DEFAULT VALUES. If you supply one (even if it includes all the columns in the table), your INSERT will fail.

As with the DEFAULT value keyword, DEFAULT VALUES supplies NULLs for NULLable fields without defaults. And no special handling is required to use it with identity columns—it works as you would expect.

The third form of the INSERT command retrieves values for the table from a SELECT statement. Here’s the syntax:

Since Transact-SQL’s SELECT statement doesn’t require that you include a FROM clause, the data may or may not come from another table. Here’s an example:

This example uses a UNION to add a row to those already in the source table.

The fourth form of the INSERT command allows the result set returned by a stored procedure or a SQL statement to be “trapped” in a table. Here’s its syntax:

And here’s an example of how to use it:

The ability to load the results of a SQL command into a table affords a tremendous amount of power and flexibility in terms of formatting the result set, scanning it for a particular row, or performing other tasks based on it.

One interesting characteristic of the INSERT command is its imperviousness to fatal command batch errors. An INSERT that fails due to a constraint or invalid duplicate value will not cause the command batch to fail. If a group of INSERTs are executed within a command batch and one of them fails, the other INSERTs will not be affected. This is as it should be; otherwise, loading large amounts of data using INSERT statements would be greatly complicated.

If you want the whole command batch to fail when an INSERT fails, check the @@ERROR automatic variable after each INSERT and respond accordingly. Here’s an example:

On a related note, another interesting aspect of the INSERT command is its ability to remove duplicate rows by way of a unique index with the IGNORE_DUP_KEY option set. That is, if you insert a set of rows into a table with an IGNORE_DUP_KEY index, rows that violate the index’s unique constraint will be rejected without causing the other inserts to fail. So, in order to remove duplicate rows from a table, you can create a work table that’s identical in structure to it, then build an IGNORE_DUP_KEY index over the second table that includes all the first table’s candidate keys and insert the table’s rows into it. Here’s an example:

Notice that we can’t include the notes column in the index because, as a text column, it’s not a valid index key candidate. This notwithstanding, the inclusion of the other columns still provides a reasonable assurance against duplicates.

A table without a clustered index is known as a heap table. Rows inserted into a heap table are inserted wherever there’s room in the table. If there’s no room on any of the table’s existing pages, a new page is created and the rows are inserted onto it. This can create a hotspot at the end of the table (meaning that users attempting simultaneous INSERTs on the table will vie for the same resources). To alleviate the possibility of this happening, you should always establish clustered indexes for the tables you build. Consider using a unique key that distributes new rows evenly across the table. Avoid automatic, sequential, clustered index keys as they can cause hotspots, too. Going from a heap table to a clustered index with a monotonically increasing key is not much of an improvement. Also avoid nonunique clustered index keys. Prior to SQL Server 7.0, they caused the creation of overflow pages as new rows with duplicate keys were inserted, slowing the operation and fragmenting the table. Beginning with version 7.0, a “uniqueifier” (a four-byte sequence number) is appended to each duplicate clustered index key in order to force it to be unique. Naturally, this takes some processing time and is unnecessary if you use unique keys in the first place. As with all indexing, try to use keys that balance your need to access the data with your need to modify it.

In addition to standard INSERTs, Transact-SQL supports bulk data loading via the BULK INSERT command. BULK INSERT uses the BCP (Bulk Copy Program) facility that’s been available in SQL Server for many years. Prior to its addition to Transact-SQL, developers called the external bcp utility using xp_cmdshell or accessed the Distributed Management Objects (DMO) API in order to bulk load data from within Transact-SQL. With the addition of the BULK INSERT command to the language itself, this is now largely unnecessary. Here’s an example: