0

I'm new to SQL Server and was trying some SQL statements for my database.

I want to enable identity insert with the following statement:

SET IDENTITY_INSERT tableName ON

That works fine. After that I inserted an entry but if I close the script the changes are lost.

I also tried 

 EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? ON",
                        @whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id AND is_identity = 1) and o.type = ''U'''

to enable identity insert for all tables, but that did not work either.

My expectation was that I run that script once and it will change the DB for all time but if I try to insert some entries with identity column, it doesn't insert the entry.

Felix Arnold
  • 839
  • 7
  • 35
  • Possible duplicate of [What is the scope of SET IDENTITY\_INSERT xyz ON?](https://stackoverflow.com/questions/5791941/what-is-the-scope-of-set-identity-insert-xyz-on) – Diado Jan 17 '19 at 12:52
  • 2
    You can only have one table with `IDENTITY_INSERT` enabled per session (see the [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017)). If you want to enable it for all tables, all of the time, why are you using `IDENTITY` columns? – Diado Jan 17 '19 at 12:54
  • 1
    If you want to turn off the identity nature of a column on all tables, forever, use `ALTER TABLE` and change the table's identity column's definition to no longer be identity. – MatBailie Jan 17 '19 at 13:04
  • Ok, thanks! Can someone provide an example, how to ```ALTER TABLE``` the identity column to be not identity, without deleting all tables and re-create them? – Felix Arnold Jan 17 '19 at 13:26
  • You cannot remove the identity attribute from a column using alter table (or any other tsql command). You must drop the column and then add it back without the identity attribute. Given your question, the simpler approach is to recreate your database since we can assume that your identity columns are also primary keys. An example of how to remove identity is [here](https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table) – SMor Jan 17 '19 at 13:56
  • yeah that one i found also, thanks – Felix Arnold Jan 17 '19 at 14:13

0 Answers0