Marius Vasile

Marius Vasile

  • 586
  • 1.8k
  • 136.8k

INSERT INTO procedure with a small error

Nov 25 2023 6:38 AM

I am trying to copy data from one table to another but with new ID, kind of duplicating entries for a new row. Data is copied correctly except but the FK which is not copied correctly. The 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
		SET @EIDNew = SCOPE_IDENTITY();

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

The first table is updated corectly, see the ID

 

but the second table only insert last ID as FK

and I don't understand why?


Answers (8)