troubleshooting Question

Set Insert Identity problems with multiple tables.

Avatar of nazzie303
nazzie303 asked on
Microsoft SQL Server
3 Comments1 Solution2255 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
adathelad

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros