CODEWITHSUNDEEP
phpsqlt-sqlinsertidentity
Miguel Mas
Miguel Mas

Reputation: 545

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.

Upvotes: 1

Views: 923

Answers (2)

HEEN
HEEN

Reputation: 4727

IDENTITY column is automatically incremented. You just need to specify the starting value and at what seed it will increment while creating the table. THAT's it.

So you cannot insert values for the IDENTITY column, as SQL server handles it for you.

ID int identity(1, 2) -- here (1 is starting value and 2 it will auto-increment by 2)

So it will look something like.

1

3

5

7

For more info, have a look at here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270613

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);

Upvotes: 3

Related Questions