asked on

Set Insert Identity problems with multiple tables.

Why do I get this error when repeating this the code below for multiple tables?
I know it has somthing to do with multiple tables, because this works for one table only.

Error:
IDENTITY_INSERT is already ON for table 'PortalQA.dbo.AccountPreferences'. Cannot perform SET operation for table 'AccountRoles'.
Server: Msg 8107, Level 16, State 1, Line 7

IDENTITY_INSERT is already ON for table 'PortalQA.dbo.AccountPreferences'. Cannot perform SET operation for table 'AccountRoles'.

This is what I'm doing:
I create two tables, set insert identity on, insert data, set insert identity off

code:

      CREATE TABLE [dbo].[AccountPreferences] (
            [AccountPreferenceID] [int] IDENTITY (1, 1) NOT NULL ,
            [AccountID] [int] NOT NULL ,
            [DateFormatID] [int] NULL ,
            [SkinID] [int] NULL ,
            [LastPageVisitedURL] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [LastPageVisitedPageID] [int] NULL ,
            [StartHerePageURL] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [isPasswordRemembered] [bit] NOT NULL ,
            [isArchived] [int] NOT NULL ,
            [InsertedTimestamp] [datetime] NOT NULL ,
            [InsertedByAccountID] [int] NULL ,
            [UpdatedTimestamp] [datetime] NULL ,
            [UpdatedByAccountID] [int] NULL
      ) ON [PRIMARY]
      GO

      CREATE TABLE [dbo].[AccountRoles] (
            [AccountRoleID] [int] IDENTITY (1, 1) NOT NULL ,
            [AccountID] [int] NOT NULL ,
            [RoleID] [int] NOT NULL ,
            [isArchived] [int] NOT NULL ,
            [InsertedTimestamp] [datetime] NOT NULL ,
            [InsertedByAccountID] [int] NULL ,
            [UpdatedTimestamp] [datetime] NULL ,
            [UpdatedByAccountID] [int] NULL
      ) ON [PRIMARY]
      GO

SET IDENTITY_INSERT AccountPreferences ON
SET IDENTITY_INSERT AccountRoles ON

INSERT INTO AccountPreferences ( AccountPreferenceID , AccountID , DateFormatID , SkinID , LastPageVisitedURL , LastPageVisitedPageID , StartHerePageURL , isPasswordRemembered , isArchived , InsertedTimestamp , InsertedByAccountID , UpdatedTimestamp , UpdatedByAccountID ) VALUES ( '9' , '6' , '14' , '11' , 'http://localhost/Portal/Default.aspx' , '26' , 'http://localhost/Portal/Default.aspx' , '0' , '0' , '12/30/2002 8:24:50 PM' , '' , '5/14/2003 6:47:55 PM' , '' )
INSERT INTO AccountRoles ( AccountRoleID , AccountID , RoleID , isArchived , InsertedTimestamp , InsertedByAccountID , UpdatedTimestamp , UpdatedByAccountID ) VALUES ( '43' , '6' , '1' , '0' , '11/20/2002 12:52:27 PM' , '' , '4/13/2003 8:32:54 PM' , '' )
INSERT INTO AccountRoles ( AccountRoleID , AccountID , RoleID , isArchived , InsertedTimestamp , InsertedByAccountID , UpdatedTimestamp , UpdatedByAccountID ) VALUES ( '141' , '6' , '2' , '0' , '11/28/2002 4:22:03 PM' , '' , '4/13/2003 8:32:54 PM' , '' )

SET IDENTITY_INSERT AccountPreferences OFF
SET IDENTITY_INSERT AccountRoles OFF

rajesh009

Hi,

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

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.

Thanks,
Rajesh.

ASKER CERTIFIED SOLUTION

adathelad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nazzie303

ASKER

Hi,

Thanks for the answers. This is what I feared from the beginning. Now I have to do the
script manually. Before I was getting a list of tables and I was appending ON or OFF.

Thanks.

Andrzej