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