Identity_Insert Is Already on for Table 'X'. Cannot Perform Set Operation for Table 'Y'

IDENTITY_INSERT is already ON for table 'X'. Cannot perform SET operation for table 'Y'

Allow SQL Server to insert the identity values automatically for you. Since this is a trigger, there could multiple rows being inserted at a time. For one row inserts, you can use SCOPE_IDENTITY() function (http://msdn.microsoft.com/en-us/library/ms190315.aspx) to retrieve the identity value of your last inserted row. However, since we could have multiple rows inserted in a trigger, we will use the OUTPUT clause (http://msdn.microsoft.com/en-us/library/ms177564.aspx) to get back a list of the inserted IdAlarme values for each idRegisto.

I'm assuming that alarmes.IdAlarme and sensores_em_alerta.id_sensores_em_alerta are the two identity fields in this trigger. If that is the case, then this should work:

CREATE TRIGGER Alert ON registos AFTER INSERT AS
BEGIN

DECLARE @comp decimal = 0
DECLARE @id_sensores_em_alerta decimal
DECLARE @tempmin decimal = 0
DECLARE @temp decimal = (SELECT s.lim_inf_temp  from sensores s JOIN inserted i ON s.idSensor=i.idSensor )

DECLARE @tblIdAlarme TABLE (idRegisto int not null, IdAlarme int not null);

-- Insert into alarmes from the inserted rows if temperature less than tempmin
--  IdAlarme is identity field, so allow SQL Server to insert values automatically.
--      The new IdAlarme values are retrieved using the OUTPUT clause http://msdn.microsoft.com/en-us/library/ms177564.aspx
INSERT alarmes (descricao_alarme,data_criacao, idRegisto)
OUTPUT inserted.idRegisto, inserted.IdAlarme INTO @tblIdAlarme(idRegisto, IdAlarme)
    SELECT descricao_alarme = 'temp Error', data_criacao = GETDATE(), i.idRegisto
    FROM inserted AS i
    WHERE i.Temperatura < @temp
;

--It looks like this table needs a PK on both idSensor and idAlarme fields, or else you will get an error here 
--  if an alarm already exists for this idSensor.
INSERT INTO sensores_tem_alarmes(idSensor,idAlarme,dataAlarme) 
SELECT i.idSensor, a.IdAlarme, dataAlarme = GETDATE()
FROM inserted i
INNER JOIN @tblIdAlarme a ON i.idRegisto = a.idRegisto
;

--not sure what this is doing?? Will always be 1.
SET @comp += 1;

--id_sensores_em_alerta is an identity field, so allow SQL Server to insert values automatically
INSERT INTO sensores_em_alerta(idSensor, idAlarme, data_registo, numerosensoresdisparados) 
SELECT i.idSensor, a.IdAlarme, data_registo = GETDATE(), numerosensoresdisparados = @comp
FROM inserted i
INNER JOIN @tblIdAlarme a ON i.idRegisto = a.idRegisto
;

END

Confusing IDENTITY_INSERT error, says it is already on in another table

You can only set IDENTITY_INSERT on for one table at a time in your session. If you want to use it on multiple tables, you need to set it ON and OFF consistently, see the template below:

SET IDENTITY_INSERT TableName ON

--insert values with IDs

SET IDENTITY_INSERT TableName OFF 

SET IDENTITY_INSERT PropertyAssets ON

--insert value with IDs

SET IDENTITY_INSERT PropertyAssets OFF

Set IDENTITY_INSERT ON is not working

You might be just missing the column list, as the message says

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON

INSERT INTO [MyDB].[dbo].[Equipment]
            (COL1,
             COL2)
SELECT COL1,
       COL2
FROM   [MyDBQA].[dbo].[Equipment]

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF 

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server

Summary

SQL Server won't let you insert an explicit value in an identity column unless you use a column list. Thus, you have the following options:

1. Make a column list (either manually or using tools, see below)

OR

2. make the identity column in tbl_A_archive a regular, non-identity column: If your table is an archive table and you always specify an explicit value for the identity column, why do you even need an identity column? Just use a regular int instead.

---

Details on Solution 1

Instead of

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

you need to write

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

with field1, field2, ... containing the names of all columns in your tables. If you want to auto-generate that list of columns, have a look at Dave's answer or Andomar's answer.

---

Details on Solution 2

Unfortunately, it is not possible to just "change the type" of an identity int column to a non-identity int column. Basically, you have the following options:

• If the archive table does not contain data yet, drop the column and add a new one without identity.

OR

• Use SQL Server Management Studio to set the Identity Specification/(Is Identity) property of the identity column in your archive table to No. Behind the scenes, this will create a script to re-create the table and copy existing data, so, to do that, you will also need to unset Tools/Options/Designers/Table and Database Designers/Prevent saving changes that require table re-creation.

OR

• Use one of the workarounds described in this answer: Remove Identity from a column in a table

IDENTITY_INSERT is set to OFF - How to turn it ON?

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

SET IDENTITY_INSERT ON/OFF needed on application server, but ALTER permission seems dangerous. Suggestion?

Starting with SQL Server 2012 you can use sequences like in Oracle. You may be better off with those. First, create the sequence:

CREATE SEQUENCE mySeq AS LONG START WITH 1 INCREMENT BY 1;
GO

Then have the table's primary key default to the next sequence value (instead of being an IDENTITY value):

CREATE TABLE myTable (
   myPK LONG PRIMARY KEY DEFAULT (NEXT VALUE FOR mySeq),
   myWhatever...
);

If you don't specify a PK value with an INSERT you'll get a unique, generated sequence value. It's basically the same behavior as an IDENTITY. But if you want to specify a PK value you can, as long as you don't violate the primary key's uniqueness - but again, that's the same behavior as an IDENTITY with SET IDENTITY INSERT ON.

---

Related Topics