1

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?

Improve this question
8
  • 1
    [sql-server] and [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. Commented Nov 10, 2021 at 16:52
  • What is the error you are seeing? Commented Nov 10, 2021 at 16:56
  • @Larnu I am using SQL Server Commented Nov 10, 2021 at 17:02
  • 1
    See answer to prior question Commented Nov 10, 2021 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? What about it didn't you understand, so we can try to elaborate. Commented Nov 10, 2021 at 17:15

2 Answers 2

Reset to default
4

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

2 Comments

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
yes, the number of inserts is not limited by the SET operation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.