Menu
I have a script which creates an entire database and inserts all records to a few dozen tables. It works great, unless there is some issue during the processing, and a table gets left with IDENTITY_INSERT ON, when the script fails during insertion and before it can be set to OFF again.
When this happens, the script automatically fails when attempting to run it again, with the error "IDENTITY_INSERT is already ON for table xx" as we go into the insertion for the first table.
As a failsafe I would like to make sure that IDENTITY_INSERT is set to OFF for all tables, before running the rest of the processing in the setup script.
As an alternative, we could perhaps close the MS SQL connection and open it again, which, as I understand it, would clear all IDENTITY_INSERT values for the connection session.
What's the best way to do this, and prevent the "already on" errors?
367
You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.
IDENTITY_INSERT off in SQL ServerOnce 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.
The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table.
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.
Dynamic SQL:
select 'set identity_insert ['+s.name+'].['+o.name+'] off' from sys.objects o inner join sys.schemas s on s.schema_id=o.schema_id where o.[type]='U' and exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1) Then copy & paste the resulting SQL into another query window and run
115
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
