Miguel Mas Miguel Mas - 2 months ago 6
SQL Question

Cannot insert the value NULL in a identity id

Here's the table. I need the id to increment automatically.

CREATE TABLE [dbo].[Atable](
[id] INT NOT NULL IDENTITY(1, 1),
[CaseNo] [int] NULL,
CONSTRAINT [AtablePK] PRIMARY KEY CLUSTERED
(
[id] 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 IDENTITY_INSERT dbo.Atable ON


And here's the insert procedure:

CREATE PROCEDURE dbo.INSERT_Atable
@id INT = NULL OUTPUT
,@CaseNo INT = NULL

AS
SET NOCOUNT OFF;

SELECT @id = @@IDENTITY

INSERT INTO dbo.Atable(id,CaseNo) VALUES(@id ,@CaseNo)


And here's the call to the procedure:

SET IDENTITY_INSERT dbo.INSERT_Atable ON
EXEC dbo.Atable @CaseNo = '2'


Why am I getting This error?:

Cannot insert the value NULL into column 'id', table 'dbo.Atable'; column does not allow nulls. INSERT fails.

Answer

If you having an IDENTITY column, then it gets assigned a value automatically.

Don't include it in the INSERT statement:

INSERT INTO dbo.Atable(CaseNo) 
     VALUES(@CaseNo);