Execute Job, for stored procedure its return Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Question

I have problem when I execute job for stored procedure it returns an error that I can insert data into the table

Cannot insert explicit value for identity column in table 'TagMovementTable_temp' when IDENTITY_INSERT is set to OFF. [SQLSTATE 23000] (Error 544). The step failed.,00:00:00,16,544,,,,0

This is my procedure and I have already set IDENTITY_INSERT to ON, I don't know what shall I do, please any help and thoughts?

SET IDENTITY_INSERT Backup_movment.dbo.TagMovementTable_temp ON
go

ALTER procedure [dbo].[DeleteTagData]
as
begin
   insert into Backup_movment.dbo.TagMovementTable_temp([ID]
      ,[TagID]
      ,[AntennaID]
      ,[RSSI]
      ,[Timestamp]
      ,[TagData]
      ,[ZoneCoordinateGroupID]
      ,[DBTimeStamp]
      ,[BatteryStatus]
      ,[Longitude]
      ,[Latitude]
      ,[Accuracy]) 
   select 
      [ID]
      ,[TagID]
      ,[AntennaID]
      ,[RSSI]
      ,[Timestamp]
      ,[TagData]
      ,[ZoneCoordinateGroupID]
      ,[DBTimeStamp]
      ,[BatteryStatus]
      ,[Longitude]
      ,[Latitude]
      ,[Accuracy] 
   FROM 
      [TrailBlazerNG].[dbo].[TagMovementTable] 
   where not exists (select 1 
                     from Backup_movment.dbo.TagMovementTable_temp 
                     where Backup_movment.dbo.TagMovementTable_temp.ID =  [TrailBlazerNG].[dbo].[TagMovementTable].ID  )

if not exists(select top 1 * from ZoneTable where EmergencyMode=1)
begin
    delete top(10000) from TagMovementTable where [DBTimeStamp]<DATEADD(hh,-24,getdate())
end

Delete from tagstrongestpingdatatable where [dbtimestamp] < dateadd(hh,-4,getdate())

Delete from tagrawdatatable where [dbtimestamp] < dateadd(hh,-1,getdate())
end

SET IDENTITY INSERT ON and OFF should be inside the stored procedure code — Jesús López

Felix Pamittan Felix Pamittan · Accepted Answer · 2015-02-11T06:33:54

Move the SET IDENTITY_INSERT statement before the INSERT.

ALTER procedure [dbo].[DeleteTagData]
AS
BEGIN

SET IDENTITY_INSERT Backup_movment.dbo.TagMovementTable_temp ON

INSERT INTO Backup_movment.dbo.TagMovementTable_temp([ID]
  ,[TagID]
  ,[AntennaID]
  ,[RSSI]
  ,[Timestamp]
  ,[TagData]
  ,[ZoneCoordinateGroupID]
  ,[DBTimeStamp]
  ,[BatteryStatus]
  ,[Longitude]
  ,[Latitude]
  ,[Accuracy]
)  
    SELECT 
      [ID]
      ,[TagID]
      ,[AntennaID]
      ,[RSSI]
      ,[Timestamp]
      ,[TagData]
      ,[ZoneCoordinateGroupID]
      ,[DBTimeStamp]
      ,[BatteryStatus]
      ,[Longitude]
      ,[Latitude]
      ,[Accuracy] 
    FROM [TrailBlazerNG].[dbo].[TagMovementTable] 
    WHERE NOT EXISTS(
        SELECT 1 
        FROM Backup_movment.dbo.TagMovementTable_temp 
        WHERE Backup_movment.dbo.TagMovementTable_temp.ID =  [TrailBlazerNG].[dbo].[TagMovementTable].ID
    )

IF NOT EXISTS(SELECT TOP 1 * FROM ZoneTable WHERE EmergencyMode = 1)
BEGIN
    DELETE TOP(10000) FROM TagMovementTable WHERE [DBTimeStamp ] < DATEADD(hh, -24, GETDATE())
END

DELETE FROM tagstrongestpingdatatable WHERE [dbtimestamp] < DATEADD(hh, -4, GETDATE())

DELETE FROM tagrawdatatable WHERE [dbtimestamp] < DATEADD(hh, -1, GETDATE())


END

Execute Job, for stored procedure its return Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

1 Answers