I have a script for table creation here as :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PluginSettingXrefValue](
[PluginSettingXrefValueID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_PluginSettingXrefValueID] DEFAULT (newsequentialid()),
[PluginSettingInstanceID] [int] NOT NULL,
[PluginSettingID] [uniqueidentifier] NOT NULL,
[PluginSettingValue] [nvarchar](200) NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_ris_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [nvarchar](200) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](200) NULL,
[PluginID] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_PluginSettingXrefValue] PRIMARY KEY CLUSTERED
(
[PluginSettingXrefValueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
And a stored procedure to insert Values in the table :
Create Procedure [dbo].[usp_CopyPluginSettingToXrefValue]
(
@InstanceId int
)
As
Begin
declare @result as bit
IF EXISTS (SELECT * FROM PlugInSettingValues)
BEGIN
IF NOT EXISTS (SELECT * FROM PluginSettingXrefValue where PluginSettingInstanceId=@InstanceId)
BEGIN
insert into PluginSettingXrefValue(PluginSettingInstanceID,PluginSettingID,
PluginSettingValue,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,PluginID)
select @InstanceId,PlugInSettingID,PlugInSettingValue,GETDATE(),CreatedBy,null,null,PlugInID from PlugInSettingValues
set @result=1
END
END
End
RETURN @result
GO
But i am getting an error while executing the stored procedure
only and a simple insert query i.e.
insert into PluginSettingXref values('2365700a-4c0e-e411-a3e4-5c260a0854c3',1,'24065243-d2ed-40f4-a275-021dc14cf746','false','2014-07-18 12:50:49.337',NULL,NULL,NULL)
is giving an error :
An explicit value for the identity column in table 'PluginSettingXrefValue' can only be specified when a column list is used and IDENTITY_INSERT is ON.
What is going wrong here?
Setting Identity_Insert ON is not an option as it might give a room for injection attacks.