INSERT INTO procedure with a small error

I tried a different approach but the following procedure has a mistake and I don't see it

CREATE PROCEDURE [dbo].[AddDuplicate]
    @AIDNew AS INT,
    @AID AS INT
AS
BEGIN
	DECLARE @MapEvaris TABLE(OldEID int, NewEID int);

	MERGE INTO tblEvaris 
	USING (SELECT EID,Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,@AIDNew AS AID
	FROM tblEvaris WHERE tblEvaris.AID = @AID) AS Sel
	ON 1 = 0 WHEN NOT MATCHED BY TARGET THEN
	INSERT (Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,AID)
	VALUES (Sel.Componenta,Sel.FactorRisc,Sel.FormaManifestare,Sel.Gravitate,Sel.ClsGravitate,Sel.Probabilitate,Sel.ClsProbabilitate,Sel.Risc,Sel.AID)
	OUTPUT Sel.EID AS OldEID, INSERTED.EID AS NewEID INTO @MapEvaris(OldEID, NewEID);

	INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
	SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, me.NewEID
	FROM tblMasura m JOIN tblEvaris e ON m.EID = e.EID JOIN @MapEvaris AS me ON me.OldEID = e.EID WHERE e.AID = @AID
END

There was a small mistake in the last line, now the procedure works fine and it makes the changes I wanted in the first place

I modified the procedure as follow

CREATE PROCEDURE [dbo].[AddDuplicate]
    @AIDNew AS INT,
    @AID AS INT
AS
BEGIN
	BEGIN
		DECLARE @EIDNew int;
		DECLARE @InsertedEIDs Table(EID int);
		IF NOT EXISTS (SELECT 1 FROM tblEvaris WHERE AID = @AIDNew)
		INSERT INTO tblEvaris(Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,AID)
		OUTPUT INSERTED.EID INTO @InsertedEIDs(EID)
		SELECT Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,@AIDNew
		FROM tblEvaris WHERE AID = @AID 
		SET @EIDNew = (SELECT TOP 1 EID FROM @InsertedEIDs);

		INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
		SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, @EIDNew
		FROM tblMasura m JOIN tblEvaris e ON m.EID = e.EID WHERE e.AID = @AID
	END
END

it will add data into tblMasura but only the last EID added to table

The reason you might not be getting the latest identity value from the tblEvaris table using SCOPE_IDENTITY() could be due to multiple factors:

1. SCOPE_IDENTITY(): It retrieves the last identity value generated in the current session and scope. However, it only works for the current scope. If your procedure involves triggers or nested scopes that might interfere with the identity value, SCOPE_IDENTITY() might not return the expected value.

2. Concurrency Issues: If multiple insert operations are happening simultaneously, there might be a chance that another transaction inserts a record into the tblEvaris table after your check with NOT EXISTS and before the SCOPE_IDENTITY() call.

3. Other Operations: Any other operations, such as triggers or explicit changes to identity seed or increment values, could affect the identity value.

To ensure you get the latest identity value inserted into tblEvaris, you could consider using OUTPUT INSERTED.EID after the INSERT INTO tblEvaris statement to retrieve the inserted EID. Here's an example:

-- After the INSERT INTO tblEvaris statement
OUTPUT INSERTED.EID INTO @InsertedEIDs(EID);

-- Then fetch the value from @InsertedEIDs
SET @EIDNew = (SELECT TOP 1 EID FROM @InsertedEIDs);

This approach captures the inserted EID into a variable (@InsertedEIDs) and allows you to use it subsequently in your procedure. Remember to declare @InsertedEIDs as a table variable before using it.

Additionally, please review the logic of your procedure and ensure there are no conflicts or concurrency issues that might affect the identity value retrieval.

I tried first because it made more sense but it will only add data to first table and none to second. I have to translate this to sql server

Copy all data from tblMasura WHERE AID = OldAID but EID WHERE AID=NewAID

Apologize. Did you try with @AIDNew for the third solution instead of @AID? please try and let me know

As far as this procedure

CREATE PROCEDURE [dbo].[AddDuplicate]
    @AIDNew AS INT,
    @AID AS INT
AS
BEGIN
	BEGIN
		DECLARE @EIDNew int
		IF NOT EXISTS (SELECT 1 FROM tblEvaris WHERE AID = @AIDNew)
		INSERT INTO tblEvaris(Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,AID)
		SELECT Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,@AIDNew
		FROM tblEvaris WHERE AID = @AID

		INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
		SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, e.EID
		FROM tblMasura m JOIN tblEvaris e ON m.EID = e.EID WHERE e.AID = @AIDNew
	END
END

will add values to tblMasura but EID will be old ones, not new

Thank you Prasad, first query is doing the same as mine. The second is throwing an error, I think because of

SET @EIDNew = (SELECT EID FROM tblEvaris WHERE AID = @AIDNew)

the result is not a single value

For the third solution I tried it but it doesn't insert values in second table

please use the below modified script and see how it behaves.

CREATE PROCEDURE [dbo].[AddDuplicate]
    @AIDNew AS INT,
    @AID AS INT
AS
BEGIN
    BEGIN
        DECLARE @EIDNew INT

        -- Insert into tblEvaris with the new AID
        IF NOT EXISTS (SELECT 1 FROM tblEvaris WHERE AID = @AIDNew)
        BEGIN
            INSERT INTO tblEvaris (Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, AID)
            SELECT Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, @AIDNew
            FROM tblEvaris
            WHERE AID = @AID
            SET @EIDNew = SCOPE_IDENTITY();
        END
        ELSE
        BEGIN
            SET @EIDNew = (SELECT EID FROM tblEvaris WHERE AID = @AIDNew)
        END

        -- Insert into tblMasura with the new EID reference
        INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
        SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, @EIDNew
        FROM tblMasura m
        JOIN tblEvaris e ON m.EID = e.EID
        WHERE e.AID = @AID
    END
END

or

you can directly use the EID from the tblEvaris table without relying on SCOPE_IDENTITY(). Here's the modified procedure:

CREATE PROCEDURE [dbo].[AddDuplicate]
    @AIDNew AS INT,
    @AID AS INT
AS
BEGIN
    BEGIN
        DECLARE @EIDNew INT

        -- Get EID for the new AID or use existing one if AID exists
        IF NOT EXISTS (SELECT 1 FROM tblEvaris WHERE AID = @AIDNew)
        BEGIN
            -- Insert into tblEvaris with the new AID
            INSERT INTO tblEvaris (Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, AID)
            SELECT Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, @AIDNew
            FROM tblEvaris
            WHERE AID = @AID
            
            -- Get the EID for the new AID
            SET @EIDNew = (SELECT EID FROM tblEvaris WHERE AID = @AIDNew)
        END
        ELSE
        BEGIN
            -- If AID exists, get the existing EID
            SET @EIDNew = (SELECT EID FROM tblEvaris WHERE AID = @AIDNew)
        END

        -- Insert into tblMasura with the new or existing EID
        INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
        SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, @EIDNew
        FROM tblMasura m
        JOIN tblEvaris e ON m.EID = e.EID
        WHERE e.AID = @AID
    END
END

OR

Yes, you can directly use e.EID from the tblEvaris table in the INSERT INTO...SELECT statement for tblMasura. However, make sure that e.EID from tblEvaris will refer correctly to the newly inserted or existing EID for the given @AIDNew.

Here's how you might use it:

INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID)
SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, e.EID
FROM tblMasura m
JOIN tblEvaris e ON m.EID = e.EID
WHERE e.AID = @AID

This code snippet performs an INSERT INTO...SELECT operation into tblMasura, using e.EID from tblEvaris. It will select records from tblMasura and join them with tblEvaris based on the EID relationship where e.AID matches the provided @AID.

Just ensure that e.EID corresponds to the correct EID (either the newly inserted one or the existing one) for the given @AIDNew or @AID. Adjust the logic within the stored procedure accordingly to meet your exact requirements and to ensure data integrity.