Identity Column not working

Question

i'm having problem insert record to a particular table regarding identity. But SQL always tell to me that I need to turn on identity_insert but I already set identity column for that table so that i'm sure it will not produce multiple id. By the way my table has records already. Here's my code

declare @empid int
declare @trans_name varchar(max) = 'Append'
declare @lname varchar(max)
declare @fname varchar(max)
declare @mname varchar(max)
declare @emp_id varchar(max)
declare @gender varchar(max)
declare @bday datetime
declare @allowance numeric(18,2)
declare @emp_sysid numeric(18,0)
declare @datehired datetime
declare @status varchar(max)
declare @positionid numeric(18,0)

declare cc_cur cursor for
    select emps.LNAME, emps.MNAME, emps.FNAME, emps.EMP_ID,
    emps.GENDER, emps.BDAY, emps.ALLOWANCE, emps.SYSID, 
    emps.DATE_HIRED, emps.[STATUS], emps.POSITION_SYSID
    from SPADA.dbo.M_EMPLOYEE emps
        where emps.SYSID not in (select ISNULL(B.SPADAEmpID, 0) from SPADA_FIS.dbo.HRIS_Employees B
                                inner join SPADA_FIS.dbo.HRIS_EmployeeStatus C
                                    on C.EmployeeID = B.EmployeeID where C.IsCurrent = 1)

open cc_cur
fetch next from cc_cur
into @lname, @mname, @fname, @emp_id, 
     @gender, @bday, @allowance, @emp_sysid, 
     @datehired, @status, @positionid

begin tran @trans_name
    while @@FETCH_STATUS = 0
    begin
        insert into SPADA_FIS.dbo.HRIS_Employees
        (LastName, MiddleName, FirstName, OtherName,
        Gender, BirthDate, Allowance)
        values
        (@lname, @mname, @fname, @emp_id,
        @gender, @bday, @allowance)

        set @empid = SCOPE_IDENTITY()

        insert into SPADA_FIS.dbo.HRIS_EmployeeStatus
        (EmployeeID, EmploymentTypeID, DepartmentID, EmploymentTenureID,
        Remarks, DateHired, JobOrganizationID, EmployeeStatusID, IsCurrent,
        PayFrequencyID, TaxExemptionStatus)
        values
        (@empid, 1, 6, (select htet.EmploymentTenureID 
                                from SPADA_FIS.dbo.HRIS_tblEmploymentTenures htet
                                    where htet.Tenure = @status),
        'Migrated Data', @datehired, (select htjo.JobOrganizationID from SPADA_FIS.dbo.HRIS_tblJobOrganizations htjo
                                            where htjo.Position = (select pos.POSITION from SPADA.dbo.M_POSITION pos
                                                where pos.SYSID = @positionid) or htjo.Position = 
                                                    (select pos2.[DESCRIPTION] from SPADA.dbo.M_POSITION pos2
                                                        where pos2.SYSID = @positionid)),
        1, 1, 2, 2)
        fetch next from cc_cur
        into @lname, @mname, @fname, @emp_id, 
             @gender, @bday, @allowance, @emp_sysid, 
             @datehired, @status, @positionid
    end
    close cc_cur
    deallocate cc_cur
if @@ERROR <> 0
rollback tran @trans_name
commit tran @trans_name

If i set identity_insert to on.. the identity column only produces value of 1.

This is the structure of my table

USE [SPADA_FIS]
GO

/****** Object:  Table [dbo].[HRIS_EmployeeStatus]    Script Date: 03/07/2012 17:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[HRIS_EmployeeStatus](
    [EmployeeStatusID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NULL,
    [EmploymentTypeID] [int] NULL,
    [DepartmentID] [int] NULL,
    [EmploymentTenureID] [int] NULL,
    [Remarks] [varchar](max) NULL,
    [DateHired] [datetime] NULL,
    [JobOrganizationID] [int] NULL,
    [SectionID] [int] NULL,
    [EmploymentStatusID] [int] NULL,
    [IsCurrent] [bit] NULL,
    [StartDate] [datetime] NULL,
    [IsUnionMember] [bit] NULL,
    [EndDate] [datetime] NULL,
    [PayFrequencyID] [int] NULL,
    [TaxExemptionStatus] [bigint] NULL,
    [IDNumber] [varchar](50) NULL,
    [BiometricNumber] [varchar](50) NULL,
    [BankAccountNumber] [varchar](50) NULL,
    [CreatedBy_UserID] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedBy_UserID] [int] NULL,
    [UpdatedDate] [datetime] NULL,
 CONSTRAINT [PK_HRIS_EmployeeStatus] PRIMARY KEY CLUSTERED 
(
    [EmployeeStatusID] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_EmployeeID]  DEFAULT ((0)) FOR [EmployeeID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_EmploymentTypeID]  DEFAULT ((0)) FOR [EmploymentTypeID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_DepartmentID]  DEFAULT ((0)) FOR [DepartmentID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_EmploymentTenureID]  DEFAULT ((0)) FOR [EmploymentTenureID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_DateHired]  DEFAULT (getdate()) FOR [DateHired]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_JobOrganizationID]  DEFAULT ((0)) FOR [JobOrganizationID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_SectionID]  DEFAULT ((0)) FOR [SectionID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_EmploymentStatusID]  DEFAULT ((0)) FOR [EmploymentStatusID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_IsCurrent]  DEFAULT ((1)) FOR [IsCurrent]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_StartDate]  DEFAULT (getdate()) FOR [StartDate]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_IsUnionMember]  DEFAULT ((0)) FOR [IsUnionMember]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_EndDate]  DEFAULT (getdate()) FOR [EndDate]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_PayFrequencyID]  DEFAULT ((0)) FOR [PayFrequencyID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_TaxExemptionStatus]  DEFAULT ((0)) FOR [TaxExemptionStatus]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_CreatedBy_UserID]  DEFAULT ((0)) FOR [CreatedBy_UserID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_UpdatedBy_UserID]  DEFAULT ((0)) FOR [UpdatedBy_UserID]
GO

ALTER TABLE [dbo].[HRIS_EmployeeStatus] ADD  CONSTRAINT [DF_HRIS_EmployeeStatus_UpdatedDate]  DEFAULT (getdate()) FOR [UpdatedDate]
GO

don't mention the `@emp_id` when doing inserts, just name all the other columns and then the ID value will be created automatically. — Seph, Mar 07 '12 at 08:59
Identity_insert only needs to be on if you want to explicitly provide a value for the identity column. Normally you want to let it be assigned automatically. You haven't provided the schema to say which is the identity column — kaj, Mar 07 '12 at 09:00
you are trying to insert value in identity column that is why it is happening — Nighil, Mar 07 '12 at 09:01
@KAJ how can i do that?? providing schema for identity column — Rob, Mar 07 '12 at 09:02
@Nighil no I excluded the identity column for HRIS_EmployeeStatus — Rob, Mar 07 '12 at 09:03
@Rob post which tables have identities and what the columns are — Seph, Mar 07 '12 at 09:08
then what is happening is it working?post your table structure — Nighil, Mar 07 '12 at 09:08

score 3 · Accepted Answer · answered Mar 07 '12 at 09:33

That's your problem: you have:

CREATE TABLE [dbo].[HRIS_EmployeeStatus](
    [EmployeeStatusID] [int] IDENTITY(1,1) NOT NULL,

and in your INSERT statement, you are trying to insert something into that column:

insert into SPADA_FIS.dbo.HRIS_EmployeeStatus
        (EmployeeID, EmploymentTypeID, DepartmentID, EmploymentTenureID,
        Remarks, DateHired, JobOrganizationID, EmployeeStatusID, IsCurrent,
                                               *****************
        PayFrequencyID, TaxExemptionStatus)

Don't do that ! The IDENTITY column will be handled by SQL Server itself. Just remove that column from the INSERT statement (and the value from the VALUES collection) and you should be fine.

alwayslearning · Answer 2 · 2012-03-07T09:09:15.647

1

It seems that you are attempting to insert a value in a column that is marked as an 'identity' column. Hence you get the error. Identity column values are automatically generated by the server when you insert a new row.

In case you want to explicitly insert a value for such a column you need to set the 'identity_insert' property to 'true'

edited Mar 07 '12 at 09:09

answered Mar 07 '12 at 09:02

alwayslearning

4,493
6
35
47

score 0 · Answer 3 · answered Mar 07 '12 at 09:33

Ah, good old HR databases, almost like in text books... You'll need to remove 'EmployeeStatusId' from

insert into SPADA_FIS.dbo.HRIS_EmployeeStatus...

as it's defined as

[EmployeeStatusID] [int] IDENTITY(1,1) NOT NULL,

so it will be automatically generated on insert.

score 0 · Answer 4 · answered Mar 07 '12 at 10:01

The answer as already provided is not to explicitly provide a value for Employee StatusId

As a separate point you should probably drop the default constraint on EmployeeStatusId

You shouldn't be able to create a default constraint on an identity column...

Identity Column not working

4 Answers4