0

To insert values into the identity column manually, I use:

SET identity_insert product ON

INSERT INTO product (PID, ProdName, Qty, Unitprice) 
VALUES (10, 'soap', 5400, 22)

Firstly I have deleted the 10th row/record, then I have used this command to insert the identity value and record it manually. this command is inserting the record. It's fine

Where should I write the command SET identity_insert product OFF? And what is the use of the SET identity_insert product OFF command?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
usmansharif shaik
  • 65
  • 8
  • 1
    [[tag:sql-server]] and [[tag:sqlite]] are completely different products; which are you *really* using. The use of `GO` suggests an IDE like SSMS or ADS; which is normally used for SQL Server. – Thom A Nov 10 '21 at 16:52
  • What is the error you are seeing? – IMSoP Nov 10 '21 at 16:56
  • @Larnu I am using SQL Server – usmansharif shaik Nov 10 '21 at 17:02
  • @IMSoP where should i use this function SET identity_insert product OFF and what is use of this function? – usmansharif shaik Nov 10 '21 at 17:08
  • 1
    See [answer](https://stackoverflow.com/a/7063527/12859753) to [prior question](https://stackoverflow.com/q/7063501/12859753) – Bill Jetzer Nov 10 '21 at 17:14
  • 3
    *"where should i use this function"* `SET IDENTITY_INSERT` isn't a function. A function would be like `GETDATE()` or `ISNULL()`. As for how to use it, have you read the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15)? What about it didn't you understand, so we can try to elaborate. – Thom A Nov 10 '21 at 17:15
  • @Bill to be fair, that shows how to do it, but I feel like this question asks _why_. – Aaron Bertrand Nov 10 '21 at 17:47
  • Aside: The Real Question℠ is why do you want to override an `identity` column? In general you shouldn't be concerned about what identity value is assigned to a row. If there is some reason that a deleted row's identity value _must_ be reused then perhaps you have an [XY Problem](http://xyproblem.info/). – HABO Nov 10 '21 at 20:33

2 Answers2

3

From the documentation:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

So, if you are in a batch where you want to override the auto-generated identity values on two different tables, you would set the first one to OFF before setting the second one to ON.

(Also, like a lot of things, if you change something from the default, it's never a bad idea to change it back.)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

from https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

so you should set it off before you set it on to another table in your session.

Павел Сивоплясов
  • 132
  • 4
  • can I insert two or more records at a time for 1 table in the identity column? SET identity_insert product ON INSERT INTO product (PID, ProdName, Qty, Unitprice) VALUES (10, 'soap', 5400, 22) INSERT INTO product (PID, ProdName, Qty, Unitprice) VALUES (11, 'pencil', 4400, 12) go SET identity_insert product OFF – usmansharif shaik Nov 11 '21 at 04:43
  • 1
    yes, the number of inserts is not limited by the SET operation. – Павел Сивоплясов Nov 11 '21 at 06:04