Fix

The IDENTITY attribute is a column identifier to have a unique value in a table. An identity column can have data types like INT, SMALLINT, BIGINT, TINYINT, or NUMERIC. An IDENTITY_INSERT is a table property that helps explicitly to insert values into the IDENTITY column.

Error

IDENTITY_INSERT is already ON for table <first table_name>. Cannot perform SET operation for table <second table_name>.

The IDENTITY_INSERT property can be used with either ON or OFF options. We cannot set the IDENTITY_INSERT ON for more than one table in a single session in SQL Server. If you ever try to switch the IDENTITY_INSERT ON for the second table in the same session, then you might notice an error something as above. Simply run an IDENTITY_INSERT OFF on your first table to fix the error.

set identity_insert <your_first_table> off --table 1 -- hospital

Example case scenario

I will be taking two tables in my example, hospital, and employee. Here are the steps.

Step 1: I am running an IDENTITY_INSERT ON on hospital table

set identity_insert hospital on --table 1 -- hospital

Step 2: Also, I am running an IDENTITY_INSERT ON on employee table after running on hospital table. After this step, the SQL Server will immediately give an error.

set identity_insert employee on --table 2 -- employee

If you notice that the moment I run the IDENTITY_INSERT on the second table, the error occurs. It means that we must switch OFF the IDENTITY_INSERT on the hospital table.

Bottomline

IDENTITY_INSERT is a property at a table level that is used for explicitly inserting value in the identity column. IDENTITY_INSERT ON/OFF is valid on a single table on a single transaction. Before going on to the second table for explicit identity column value insertion, once cross verify that you ran the IDENTITY_INSERT OFF on your first table.

Some of the identity-related posts for your reference:
How to Add or Remove Identity Property on Column from a table in SQL server
Reset Identity Column Values In SQL Server