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.AccountPrefe
Server: Msg 8107, Level 16, State 1, Line 7
IDENTITY_INSERT is already ON for table 'PortalQA.dbo.AccountPrefe
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_
[LastPageVisitedPageID] [int] NULL ,
[StartHerePageURL] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_
[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
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.AccountPrefe
Server: Msg 8107, Level 16, State 1, Line 7
IDENTITY_INSERT is already ON for table 'PortalQA.dbo.AccountPrefe
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_
[LastPageVisitedPageID] [int] NULL ,
[StartHerePageURL] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_
[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
Microsoft SQL Server
Last Comment
nazzie303
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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.