This error occurs when the user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement. Let us learn about the IDENTITY_INSERT.
Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
{YOUR SQL INSERT STATEMENT}
After your SQL Statement:
SET IDENTITY_INSERT <tablename> OFF
Let me know if this resolves the problem for you. I honestly think identity should be used very carefully and they should not reset without proper purpose.
Here are few additional references for you to follow up on the subject of the identity:
- SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
- SQL SERVER – DELETE, TRUNCATE and RESEED Identity
- SQL SERVER – Jump in Identity Column After Restart
- SQL SERVER – Answer – Value of Identity Column after TRUNCATE command
- SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31
- Interview Question of the Week #019 – How to Reset Identity of Table
- SQL SERVER – Finding Out Identity Column Without Using Column Name
- SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
- SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051
- SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table
Reference: Pinal Dave (https://blog.sqlauthority.com)
62 Comments. Leave new
Hi,How to display deleted rows in SQL server here no triggers and notification date only three columns in table that is id identity,name varchar(100),age int…..please suggest.