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?

Answers


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.


Need Your Help

Trying to set variable to SQL result, variable is set to single letter

php mysql

I've ran into this problem once before on a separate project and had to remove the issue all together.