How to insert into a table with just one IDENTITY column?

sql

identity-insert

default-values

sql-server

byAlex Kataev·Oct 29, 2024

To add a new row into a table with only an IDENTITY column, use an INSERT statement combined with DEFAULT VALUES:

-- Behold, the Simplicity itself!😉
INSERT INTO YourTable DEFAULT VALUES;

This will result in SQL auto-generating and assigning the next unique ID for the new row.

Detailed explanation

Exploiting DEFAULT VALUES can be really useful when dealing with a table that only contains an IDENTITY column — the unique identifier sans extra data. This column is typically used as a primary key.

The DEFAULT VALUES keyword instructs SQL Server to use the default value for all columns. For the IDENTITY column, the engine generates the next number in its sequence.

Suppose you're in a loop, and we all know it's no fun. The following command would be your break-free key to insert multiple rows:

-- Who said money can't buy love? Here you get 10 for the price of 1.
INSERT INTO YourTable DEFAULT VALUES;
GO 10

In the command above, GO stands out as a batch separator commanding SQL Server to execute the preceding statement multiple times—in this case, 10.

Navigating through nail-biters

Working with IDENTITY columns might throw you a few curveballs. Let's catch them:

Starting anew: There comes a time when you want to start the count from a new number. In such cases, you have the power to reseed the identity:
```
-- Wind the clock backwards, why don't we?
DBCC CHECKIDENT ('YourTable', RESEED, NewReseedValue);
```
The disappearing IDs: While performing bulk insertions, if a transaction fails midway or rolls back, there's a possibility of identity sequence breaks. It's advisable not to lose your sleep over these gaps, as IDENTITY values give no guarantees of continuity.
Deleted records leave voids: Record deletions also create gaps in identity numbers. Seek alternative strategies like a sequence object or write custom code for perfect consecutiveness.

Migrating with identity can lead you to a crossroads. If maintaining the identity value is crucial, you might have to toggle IDENTITY_INSERT to ON, albeit temporarily:

-- Trust me; I am an identity thief!
SET IDENTITY_INSERT YourTable ON;

INSERT INTO YourTable (IdentityColumnName)
VALUES (ExplicitIdentityValue);

SET IDENTITY_INSERT YourTable OFF;

Keep in mind that inserting explicit values into an IDENTITY column is a one-table-at-a-time game and demands certain privileges.

Stepping up the SQL game

A few insider tips to keep your inserts into IDENTITY columns as efficient and error-free:

Lockdown: High transaction tables can encounter locking issues. And we know no one wants to be locked down. Ease this by using DEFAULT VALUES to trim transaction size.
Luck favors the prepared: Ensure all inserts either wholly succeed or fail by roping them in a transaction.
Keep an eye out: Don't ignore the IDENTITY_CACHE setting tuned in your SQL Server. It can affect identity values in case of failures.