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

Answer 1

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