How to turn IDENTITY_INSERT on and off using SQL Server 2008?

sql

sql-server

identity-insert

bulk-insert

byNikita Barsukov·Oct 13, 2024

To retain values in an SQL Server table's identity column, you need to sequence commands. Turn SET IDENTITY_INSERT ON before the insert operation. After inserting, turn it back to SET IDENTITY_INSERT OFF. Remember, SQL Server only extends the hospitality of IDENTITY_INSERT to one table at a time.

Quick steps:

-- Starting the party for IDENTITY_INSERT
SET IDENTITY_INSERT dbo.FunkyTable ON;

-- The table's identity takes a short break. The user is the DJ now.
INSERT INTO dbo.FunkyTable (IdentityColumn, ...) VALUES (ExplicitValue, ...);

-- The original DJ (the table's identity) takes over. The party continues.
SET IDENTITY_INSERT dbo.FunkyTable OFF;

Pre-insert preparations

Before you jump into SET IDENTITY_INSERT action, tick off these boxes:

Verify there's an identity column in your table.
There should not be any other table hosting a IDENTITY_INSERT party in the current session.
When you're the DJ, you need to have all the tracks. Be ready with data for all non-nullable columns.
You don't want to hear the same track twice. Ensure explicit identity values are unique and don't exceed the column's range.

Step-by-step usage

SET IDENTITY_INSERT is no rocket science, but you need to follow a sequence:

Start the party - Activate IDENTITY_INSERT: Let the user take over the identity column DJ console.
```
-- User is the DJ now
SET IDENTITY_INSERT dbo.YourTable ON;
```

Spin your tracks - Insert the data: Time to play your tracks. Insert operation with explicit values for the identity column.

-- Play your unique tracks
INSERT INTO dbo.YourTable (IdentityColumn, OtherColumn) VALUES (ExplicitIdentityValue, OtherValue);

Hand it back - Deactivate IDENTITY_INSERT: Time to return DJ console. Turn off identity inserts.
```
-- Job done. Original DJ is back 
SET IDENTITY_INSERT dbo.YourTable OFF;
```

All your insert columns need to match the table's columns to avoid any compilation concerts.

Avoid stumbling blocks

Be cautious of few gotchas:

Scope: Setting IDENTITY_INSERT ON for a rocking party? Remember ending it in the same session is crucial.
Permissions: You need an exclusive VIP pass (alter table permissions) to start the IDENTITY_INSERT party.
Track Limits: Your tracks (explicit identity values) must meet the column's type requirements.
Repeated tracks: Ensure you're not repeating the tracks (identity values), as they can lead to constraint boogie nights.
Pre-party call: Before you start the IDENTITY_INSERT party, ensure to announce your party (set IDENTITY_INSERT ON) in the same session.

Coding with IDENTITY_INSERT

If you're teaming up IDENTITY_INSERT with scripts or applications, remember few things:

Validation: Don't let two IDENTITY_INSERT party overlap. Check if the previous one is done.
Execution: Make .ExecuteNonQuery() your bouncer to take care of any unwelcome issues.
Transaction: Treat IDENTITY_INSERT setting and INSERT statement as an exclusive party to ensure consistency.
Error prevention: Place IDENTITY_INSERT in a nice try / catch lounge for party-cleanups using SET IDENTITY_INSERT OFF in the finally block.

Bulk insert operation

For large VIP parties (bulk operations), these are a must:

Batch Processing: It's not practical to individually welcome every guest. Group your INSERT ... VALUES ... statements.
Bulk Insert: Let the bouncers (BULK INSERT or OPENROWSET) deal with guest-list (data files).
Minimal Logging: Large parties can be messy (heavy transaction log space), ensure you hire cleaning staff (minimal logging) for swift cleaning.