locked
Identity_Insert RRS feed

  • Question

  • Hi all,

    Is there any means of knowing the status of IDENTITY_INSERT is ON/OFF???

     

    Thanks n Regards

     

    Wednesday, July 20, 2011 5:18 AM

Answers

  • That settings is per session ...I do not think you can see what the status is.. Perhaps if you explain what you are trying to achieve we can provide more accurate suggestion 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Mr. Wharty Wednesday, July 20, 2011 7:35 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:29 AM
    Wednesday, July 20, 2011 5:20 AM
  • I agree with Uri that I don't think there is an easy way to check this, since the setting is per session if you set identity insert on after already setting it on for another table it raises an error, so you could catch the error, for example to test I created a test db and ran:

    CREATE TABLE [dbo].[Table_1](
    	[ID] [int] IDENTITY(1,1) NOT NULL
    )
    CREATE TABLE [dbo].[Table_2](
    	[ID] [int] IDENTITY(1,1) NOT NULL
    )
    GO
    
    BEGIN TRY
    
    	set identity_insert table_1 on;
    	set identity_insert table_2 on;
    
    END TRY
    
    BEGIN CATCH
    
    SELECT
    	ERROR_NUMBER() AS ErrorNumber,
    	ERROR_SEVERITY() AS ErrorSeverity,
    	ERROR_STATE() AS ErrorState,
    	ERROR_PROCEDURE() AS ErrorProcedure,
    	ERROR_LINE() AS ErrorLine,
    	ERROR_MESSAGE() AS ErrorMessage
    
    END CATCH
    

    ...and got the error_number/error_message:

    8107, "IDENTITY_INSERT is already ON for table 'TestDB.dbo.test1'. Cannot perform SET operation for table 'table_1'."

    ...so you can "find out" the status although it is not exactly elegant but the error can be handled via try/catch.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by Mr. Wharty Wednesday, July 20, 2011 7:35 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:29 AM
    Wednesday, July 20, 2011 6:04 AM

All replies

  • That settings is per session ...I do not think you can see what the status is.. Perhaps if you explain what you are trying to achieve we can provide more accurate suggestion 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Mr. Wharty Wednesday, July 20, 2011 7:35 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:29 AM
    Wednesday, July 20, 2011 5:20 AM
  • I wanted to copy the schema of one table to another and there after data..

    If by any chance the the source table from which I am copying data may have set the Identity _inset at some point of time and might have inserted duplicate values in identity column and there after might have disabled Identity_Insert option then how to reflect the source schema in target..

    I need to the status of Identity_insert in source and react accordingly.

    Thanks n Regards


    Wednesday, July 20, 2011 5:27 AM
  • I agree with Uri that I don't think there is an easy way to check this, since the setting is per session if you set identity insert on after already setting it on for another table it raises an error, so you could catch the error, for example to test I created a test db and ran:

    CREATE TABLE [dbo].[Table_1](
    	[ID] [int] IDENTITY(1,1) NOT NULL
    )
    CREATE TABLE [dbo].[Table_2](
    	[ID] [int] IDENTITY(1,1) NOT NULL
    )
    GO
    
    BEGIN TRY
    
    	set identity_insert table_1 on;
    	set identity_insert table_2 on;
    
    END TRY
    
    BEGIN CATCH
    
    SELECT
    	ERROR_NUMBER() AS ErrorNumber,
    	ERROR_SEVERITY() AS ErrorSeverity,
    	ERROR_STATE() AS ErrorState,
    	ERROR_PROCEDURE() AS ErrorProcedure,
    	ERROR_LINE() AS ErrorLine,
    	ERROR_MESSAGE() AS ErrorMessage
    
    END CATCH
    

    ...and got the error_number/error_message:

    8107, "IDENTITY_INSERT is already ON for table 'TestDB.dbo.test1'. Cannot perform SET operation for table 'table_1'."

    ...so you can "find out" the status although it is not exactly elegant but the error can be handled via try/catch.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by Mr. Wharty Wednesday, July 20, 2011 7:35 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:29 AM
    Wednesday, July 20, 2011 6:04 AM
  • Is it possible to include a unique constraint/primary key on the source/target tables identity column to avoid this situation? If there are duplicates in the source tables identity column and you are happy to copy these over then the previous setting of identity insert on the source table shouldn't matter, you will be able to include the duplicates in the target table thereby copying the data by setting identity insert on the target table, although this does not sound like an ideal situation, might be a lot easier to avoid it in the first place.

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Wednesday, July 20, 2011 6:12 AM
  • Wednesday, July 20, 2011 11:17 AM
    Answerer