I have three tables tblActivitate(AID) tblEvaris(EID) and tblMasura (MID) connected in this order one-to-many. For some data in tblActivitate, all existing data in other tables may match so instead of writing again I want to be able to cascade copy in second and third table. Kind of, copy from old MID to new MID, new EID as FK. From tblActivitate to tblEvaris I do it with
using (SqlConnection conn = new SqlConnection(connString)) { string sqlQuery = "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;SELECT SCOPE_IDENTITY();"; using (SqlCommand cmd = new SqlCommand(sqlQuery, conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@AID", ddlAN.SelectedItem.Value); cmd.Parameters.AddWithValue("@AIDNew", txtAD.Text); conn.Open(); int result = Convert.ToInt32(cmd.ExecuteScalar()); if (result > 0) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Inserted Successfully')", true); } conn.Close(); } }
and is working perfectly. Now, to write in the third table I tried with a stored procedure, easy to see mistakes in code
CREATE PROCEDURE [dbo].[AddDuplicate] @AIDNew as int, @AID as int AS DECLARE @EID int BEGIN 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 SELECT @EID = SCOPE_IDENTITY(); END DECLARE @MapMasura TABLE(OldMID int, NewMID int); BEGIN MERGE tblMasura as tm USING (SELECT MID,Masura,GravitateF,ClsGravitateF,ProbabilitateF,ClsProbabilitateF,RiscF,@EID AS EID FROM tblMasura WHERE EID = @EID) AS S ON 1 = 0 WHEN NOT MATCHED THEN INSERT (Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID) VALUES (S.Masura,S.GravitateF,S.ClsGravitateF,S.ProbabilitateF,S.ClsProbabilitateF,S.RiscF,S.EID) OUTPUT inserted.MID INTO @MapMasura; END END
but is not working as intended. Any help, please?