Menu
I've searched for this, but threads in which it appeared tended to have answers from people who didn't understand the question.
Take the following syntax:
SET IDENTITY_INSERT Table1 ON How do you do something more like this:
GET IDENTITY_INSERT Table1 I don't want to do anything whatsoever to the data in the database or to the settings to get this information though. Thanks!
446
Answers. In a given session , you can have only one table's IDENTITY_INSERT property set to ON. You can use set IDENTITY_INSERT state (on/off) only at excute or run time.
By default, SQL Server automatically inserts an increment value for an IDENTITY column, when the IDENTITY_INSERT parameter is set to OFF. If you don't need an explicit value for the IDENTITY column, remove the IDENTITY column from the component schema.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value. The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
IDENTITY_INSERT off in SQL Server Once you have turned the IDENTITY_INSERT option OFF, you cannot insert explicit values in the identity column of the table. Also, the value will be set automatically by increment in the identity column if you try to insert a new record.
Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.
Sorry, no help for this.
Great question though :)
Source: Here
Update There are ways maybe to do this, also seen in the site I linked, IMO, it is too much effort to be useful.
if (select max(id) from MyTable) < (select max(id) from inserted) --Then you may be inserting a record normally BEGIN set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE END ELSE --You definitely have IDENTITY_INSERT on. Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway BEGIN .... Code that shouldn't run with IDENTITY_INSERT on END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
