Geek Answers Handbook

IDENTITY_INSERT ON with the error "... is not a user table. Cannot perform the SET operation"

I accidentally deleted a row in the database and I would like to insert a row again. The problem is that the primary key is set to auto increment and the IDENTITY INSERTvalue is set OFFto the table. I would like to temporarily enable the identity insert so that I can insert the deleted row and then disable the identity insert.

In SQL Server Management Studio, I tried to do the following:

SET IDENTITY_INSERT myTable ON

INSERT INTO myTable (id, name, value)
VALUES (241, 'hello', 'hello2')

SET IDENTITY_INSERT myTable OFF

The first line fails:

Msg 8105, Level 16, State 1, Line 2
"myTable" is not a user table. Cannot perform SET operation.

Any idea why?

+5
1

: http://www.sql-server-performance.com/2007/not-a-user-table-cannot-perform-set-operation/

myTable .

:

SELECT * FROM sysobjects WHERE name = ‘myTable’

xtype?

+5

More articles:


All Articles