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?