SQL Server error.Explicit value for Identity Column....can only be specified when a column list is used and IDENTITY_INSERT is ON

Question

I am trying to create procedure, which is generating an error stating

An explicit value for the identity column in table tblRegisterUser can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried to surround insert statement with INDENTITY_INSERT to ON,but that too doesn't work. am I missing anything or is it an error with the sub query which i included?

Following is the stored procedure

CREATE PROCEDURE dbo.spInsertUserRegister
(
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Username nvarchar(50),
    @Password nvarchar(50),
    @Designation nvarchar(50),
    @Department nvarchar(50),
    @IsAdmin bit
)
AS
BEGIN
    INSERT INTO tblRegisterUser Values
    (
        @FirstName, @LastName, @Username, @Password,@Designation,@Department,@IsAdmin
    )
    DECLARE @UID INT
    SET @UID = @@IDENTITY
    INSERT INTO tblLogin(Username,Password,UID,IsAdmin) 
    Values(@Username, @Password, @UID,(SELECT IsAdmin FROM tblRegisterUser WHERE     Username=@Username AND Password=@Password))
END

"*that too doesn't work*" is not a valid SQL Server error message. — , Jun 10 '13 at 08:55
Unless there's a trigger changing the behaviour of your first insert, or you're allowing multiple people to have the same usernames (and, incidentally, passwords), what's the point of the `(SELECT IsAdmin FROM tblRegisterUser ...` from the second `INSERT`? Why not just `@IsAdmin` again? — Damien_The_Unbeliever, Jun 10 '13 at 09:11

score 2 · Answer 1 · edited May 23 '17 at 11:43

If the structure of the tblRegisterUser table is something like

ID int primary_key autoincrement
FirstName varchar
LastName varchar 
Username varchar
Password varchar
Designation varchar
Department varchar
IsAdmin bit

than this statement is wrong:

INSERT INTO tblRegisterUser Values
(
    @FirstName, @LastName, @Username, @Password, 
    @Designation,@Department,@IsAdmin
)

You should use an explicit column list to specify the columns:

INSERT INTO tblRegisterUser 
(  FirstName, LastName, Username, Password,
   Designation, Department, IsAdmin) 
VALUES 
(
    @FirstName, @LastName, @Username, @Password, 
    @Designation,@Department,@IsAdmin
)

This way the ID field is automatically populated, and the @@Identity statement should return it correctly.

That said, SQL Server has a few functions that return the generated ID for the last rows, each with it's own specific strengths and weaknesses.

Basically:

@@IDENTITY works if you do not use triggers
SCOPE_IDENTITY() works for the code you explicitly called.
IDENT_CURRENT(‘tablename’) works for a specific table, across all scopes.

In almost all scenarios SCOPE_IDENTITY() is what you need, and it's a good habit to use it, opposed to the other options.
A good discussion on the pros and cons of the approaches is also available here.

copied from this answer

Thanks, its working now, actually it is an identity column, but my while creating tblRegisterUser I missed a column, I must have had done some trail and errors before posting :(. sorry for posting such a silly question. — user2470174, Jun 10 '13 at 09:38

Devart · Answer 2 · 2013-06-10T09:03:30.603

Try this one -

CREATE PROCEDURE dbo.spInsertUserRegister 
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Username NVARCHAR(50),
    @Password NVARCHAR(50),
    @Designation NVARCHAR(50),
    @Department NVARCHAR(50),
    @IsAdmin BIT
)
AS BEGIN

    INSERT INTO dbo.tblRegisterUser (FirstName, LastName, Username, [Password], Designation, Department, IsAdmin)
    SELECT @FirstName, @LastName, @Username, @Password, @Designation, @Department, @IsAdmin

    DECLARE @ID BIGINT
    SELECT @ID = SCOPE_IDENTITY()

    --SET IDENTITY_INSERT dbo.tblLogin ON;

    INSERT INTO dbo.tblLogin (UserName, [password], [uid], IsAdmin)
    SELECT @Username, @Password, @ID, IsAdmin 
    FROM tblRegisterUser 
    WHERE UserName = @Username 
        AND [password] = @Password

    --SET IDENTITY_INSERT dbo.tblLogin OFF;

END

Thanks, its working now, actually it is an identity column, but my while creating tblRegisterUser I missed a column, I must have had done some trail and errors before posting :(. sorry for posting such a silly question. — user2470174, Jun 10 '13 at 09:42

SQL Server error.Explicit value for Identity Column....can only be specified when a column list is used and IDENTITY_INSERT is ON

2 Answers2