Identity_insert is set to OFF - How to turn it ON?

sql

identity-insert

sql-best-practices

data-migration

byAlex Kataev·Sep 13, 2024

To insert explicit values into an identity column, turn on IDENTITY_INSERT:

SET IDENTITY_INSERT YourTable ON;

Replace YourTable with your actual table's name. After executing the inserts, ensure to switch IDENTITY_INSERT off:

SET IDENTITY_INSERT YourTable OFF;

Note that you can only have IDENTITY_INSERT on for one table at a time per session. Also, make sure you have the correct permissions to use this statement.

Demystifying IDENTITY_INSERT

IDENTITY_INSERT gives you override ability when working with auto-incrementing primary keys. It allows you to insert explicit identity values into the identity column of a table, which can be incredibly useful during processes such as data migration or maintaining historical IDs.

Best cases for IDENTITY_INSERT usage

IDENTITY_INSERT proves valuable when:

Migrating or restoring data with the necessity to keep the original IDs.
Precisely inserting legacy data with specific ID values.

Sequence to success

Follow these four steps to avert errors when working with IDENTITY_INSERT:

Wrap your insert statement with SET IDENTITY_INSERT YourTable ON/OFF.
Avoid any GO statements; keep settings bundled in a single batch.
Use the correct table name within the statement.
Always switch OFF IDENTITY_INSERT when done.

Permission check & Test Run

Before you move ahead, don’t forget to:

Inspect user permissions – it should ideally be db_owner or db_ddladmin.
Run a test – insert a row without activating IDENTITY_INSERT to confirm the auto-increment behavior of the column.

Essential tips and troubleshooting

When dealing with IDENTITY_INSERT, it pays to be mindful of these practices and potential issues:

Best practices

Bundle up! – Encapsulate the IDENTITY_INSERT within a stored procedure for a smoother ride.
Check settings – Keep an eye on the settings of ANSI_NULLS and QUOTED_IDENTIFIER for error-free stored procedure execution.
Plan it out – Use a dedicated procedure for complex data insertions with IDENTITY_INSERT.

Tricky bits to look out for

Patience, my friend! – IDENTITY_INSERT works with one table at a time. So, no juggling!
Off on vacation – Always turn IDENTITY_INSERT OFF after you're done to avoid any unexpected behavior.
Respect the natives – Make sure the manually inserted ID values don't clash with the increment settings of your table.

It’s all about being mindful and managing your IDENTITY_INSERT use efficiently.

Practical scenarios to get you going

Here are a few practical scenarios that illustrate when and how IDENTITY_INSERT is used:

Scenario 1: Data migration express

Moving data and want to keep those IDs intact? Here's your method:

BEGIN TRANSACTION;
SET IDENTITY_INSERT new_db.dbo.new_table ON;

INSERT INTO new_db.dbo.new_table (ID_col, ...)
  SELECT ID_col, ...
  FROM old_db.dbo.old_table;

SET IDENTITY_INSERT new_db.dbo.new_table OFF;
COMMIT TRANSACTION;

Scenario 2: Legacy system integration

Integrating old with new? Keep records arranged with preserved IDs:

SET IDENTITY_INSERT integrated_table ON;

-- Let's repay some old debts 🧓
INSERT INTO integrated_table (ID_col, ...) VALUES (legacy_system_id, ...);

SET IDENTITY_INSERT integrated_table OFF;