Menu
I'm trying to rebuild a database from a text file, using VBA. To insert the ID values, I call this before sending in the INSERTs:
SET IDENTITY_INSERT TableName ON
This seems to work fine for most TableNames, but when I call it on table 'Accounts' I get an interesting error:
"IDENTITY_INSERT is already ON for table 'test.dbo.PropertyAssets'. Cannot perform SET operation for table 'Accounts'."
I'm guessing this has to do with fkey constraints or such, but does anyone know for sure?
852
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.
IDENTITY_INSERT is a table property that allows you to insert explicit values into the column of table identifiers, i.e. into the column with IDENTITY. The value of the inserted identifier can be either less than the current value or more, for example, to skip a certain interval of values.
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.
You can only set IDENTITY_INSERT on for one table at a time in your session. If you want to use it on multiple tables, you need to set it ON and OFF consistently, see the template below:
SET IDENTITY_INSERT TableName ON
--insert values with IDs
SET IDENTITY_INSERT TableName OFF
SET IDENTITY_INSERT PropertyAssets ON
--insert value with IDs
SET IDENTITY_INSERT PropertyAssets OFF
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
