How to set identity_insert to on in SQL Server

Question

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on; 

but I get the following error:

Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS
-Databases
--System Databases
--mySchema

Does anyone know a solution to this problem?

Answer 1

You are using the right syntax:

SET IDENTITY_INSERT dbo.sometable ON;

However, a schema in SQL Server is not the same as a database.

The default schema for objects is dbo as in my example. You do not need to specify the database name if you are connected to it, but you could like this:

SET IDENTITY_INSERT DbName.dbo.sometable ON;

There seems to be no way to get the current setting as it is session-scoped and not persisted.

If you run this query in your database it returns the fully qualified names for all tables in there:

SELECT 
    QUOTENAME(DB_NAME())+'.'+
    QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+
    QUOTENAME(OBJECT_NAME(object_id)) AS FullTableName 
FROM sys.tables;

SET IDENTITY_INSERT ON... must be run in the same session as the insert.