INSERT INTO procedure with a small error

Question

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?

Marius Vasile · Answer

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

Marius Vasile · Answer

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

Prasad Raveendran · Answer

The reason you might not be getting the latest identity value from the tblEvaris table using SCOPE_IDENTITY() could be due to multiple factors: 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. 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. 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.

Marius Vasile · Answer

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

Prasad Raveendran · Answer

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

Marius Vasile · Answer

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

Marius Vasile · Answer

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

Prasad Raveendran · Answer

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.

INSERT INTO procedure with a small error

Answers (8)