Trying to replicate Identity in a different column for x number of rows. SQL server Stored procedure
I want to add my identity to another column, in x number of rows. I can get this to repeat for two rows using this code in my stored procedure.
ALTER PROCEDURE [dbo].[InsertReport3] @incdate INT, @inctime VARCHAR(7), @inv1 VARCHAR(30), @inv2 VARCHAR(30), @inv3 VARCHAR(30), @inctype VARCHAR(20), @incnar TEXT, @incloc TEXT, @no INT AS INSERT INTO IncidentReport (I_Date_Incident, I_Time, IncidentType, I_Narrative, I_Location, I_ID, U_no_fk) VALUES (@incdate, @inctime, @inctype, @incnar, @incloc, @inv1, @no); UPDATE Incidentreport SET I_reportno = @@IDENTITY WHERE I_no = @@IDENTITY; INSERT INTO IncidentReport (I_reportno, I_Date_Incident, I_Time, IncidentType, I_Narrative, I_Location, I_ID, U_no_fk) VALUES (@@IDENTITY, @incdate, @inctime, @inctype, @incnar, @incloc, @inv2, @no);
Once I get up to three with that code It increments by one, only pulling the previous identity, thoughts?
Assuming that you are using MS SQL Server you should be using SCOPE_IDENTITY() instead of @@identity. Justification can be found here. I think this will fix the problem you are experiencing.