Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the CHARINDEX Function
Error Messages
Home > SQL Server Error Messages > Msg 1077 - INSERT into an identity column not allowed on table variables.
SQL Server Error Messages - Msg 1077 - INSERT into an identity column not allowed on table variables.

Error Message

Server: Msg 1077, Level 16, State 1, Line 1
INSERT into an identity column not allowed on table 
variables.

Causes

The table data type is a special data type that can be used to store a result set for processing. Variables declared as table data type are primary used as temporary storage of a set of rows returned as the result set of a table-valued function.

Table variables provide the following benefits:

  • A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure or batch that it is declared in. Within its well-defined scope, a table variable can be used like a regular table and can be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE and DELETE statements.
  • Table variables are automatically cleaned up at the end of the function, stored procedure or batch in which they are defined.
  • Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
  • Table variables require less locking and logging resources since transactions involving these variables last only for the duration of an update on the table variable.

Table variables can be referenced by name in the FROM clause. Outside a FROM clause, table variables must be referenced by using an alias.

Just like regular tables, a column in a table variable can also be declared as an IDENTITY column. But unlike regular tables, specifying an explicit value in an IDENTITY column when performing an INSERT statement is not allowed and will raise this error message.

To illustrate, here’s a script that illustrates this scenario:

DECLARE @Company TABLE (
    [CompanyID]       INT NOT NULL IDENTITY(1, 1),
    [CompanyName]     NVARCHAR(100) NOT NULL,
    [Address]         VARCHAR(100) NOT NULL,
    [City]            VARCHAR(50) NOT NULL,
    [State]           CHAR(2) NOT NULL,
    [ZIPCode]         VARCHAR(10) NOT NULL
)

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 101, 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 102, 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 103, 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )
Msg 1077, Level 16, State 1, Line 10
INSERT into an identity column not allowed on table variables.
Msg 1077, Level 16, State 1, Line 13
INSERT into an identity column not allowed on table variables.
Msg 1077, Level 16, State 1, Line 16
INSERT into an identity column not allowed on table variables.

Solution / Work Around:

In regular tables, to insert a new row in a table that contains an IDENTITY column and specifying a value to the IDENTITY column, the IDENTITY_INSERT option needs to be set for the table. The IDENTITY_INSERT option allows explicit values to be inserted into the IDENTITY column of a table. Unfortunately, the IDENTITY_INSERT option does not apply to table variables. Doing so will generate an error as can be seen in the following script:

SET IDENTITY_INSERT @Company ON

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 101, 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@Company'.

One way to overcome this error message is not to specify an explicit value for the IDENTITY column when inserting a new row in a table. Here’s how the script will look like without specifying an explicit value to the IDENTITY column:

DECLARE @Company TABLE (
    [CompanyID]       INT NOT NULL IDENTITY(1, 1),
    [CompanyName]     NVARCHAR(100) NOT NULL,
    [Address]         VARCHAR(100) NOT NULL,
    [City]            VARCHAR(50) NOT NULL,
    [State]           CHAR(2) NOT NULL,
    [ZIPCode]         VARCHAR(10) NOT NULL
)

INSERT INTO @Company ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO @Company ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO @Company ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )

Here’s the content of the table variable after the INSERT statement:

SELECT * FROM @Company
CompanyID  CompanyName  Address                 City           State ZIPCode
---------- ------------ ----------------------- -------------- ----- --------
1          Facebook     1 Hacker Way            Menlo Park     CA    94025
2          Google       1600 Amphitheatre Pkwy  Mountain View  CA    94043
3          Microsoft    One Microsoft Way       Redmond        WA    98052

Alternatively, if a value is really needed to be specified on the IDENTITY column, the only way to overcome this error message is not to specify the column as an IDENTITY column. Here’s the updated script without an IDENTITY column in the table variable:

DECLARE @Company TABLE (
    [CompanyID]       INT NOT NULL,
    [CompanyName]     NVARCHAR(100) NOT NULL,
    [Address]         VARCHAR(100) NOT NULL,
    [City]            VARCHAR(50) NOT NULL,
    [State]           CHAR(2) NOT NULL,
    [ZIPCode]         VARCHAR(10) NOT NULL
)

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 101, 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 102, 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 103, 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )

Here’s the content of the table variable after the INSERT statement:

SELECT * FROM @Company
CompanyID  CompanyName  Address                 City           State ZIPCode
---------- ------------ ----------------------- -------------- ----- --------
101        Facebook     1 Hacker Way            Menlo Park     CA    94025
102        Google       1600 Amphitheatre Pkwy  Mountain View  CA    94043
103        Microsoft    One Microsoft Way       Redmond        WA    98052
Related Articles :