Update/Delete/Insert a new record in Multiply tables

Question

I have 5 tables in my database

1.Grp 2. Mod 3.Fun 4.Grp_Mod 5.Grp_Mod_Fun
GId MId FId GId GId
GNm MNm FNm MId MId
DT DT DT DT FId

I have a list of Grp Nm
I have a list of Mod MNm
I have a list of Fun FNm

From the Grp table A GNm have to have a MNm/MId and FNm/FId asign to it.

Grp table primary key(GId) is referenced in Grp_Mod and Grp_Mod_Fun
Mod table primary key(MId) is referenced in Grp_Mod
Fun table primary key(Fid) is referenced in Grp_Mod_Fun

I have multiply GNm,MNm and FNm in each of their tables and all their Id's are referenced in the Grp_Mod_Fun.

If I want to insert a new MId and FId to a GNm base on the GId from Grp_Mod and Grp_Mod_Fun tables how do I write the sql statement for that.

Same for Update and delete.

Thanks John

Pradeep Shet · Answer

Hi I didnt got your question but still if you want to insert primary key of one table as foreign key in other then use SCOPE_IDENTITY(). this will return you the last inserted primary key of main table DECLARE @key INT Insert into MainTable values(pkkey comlumn1 column2) SET @key = SCOPE_IDENTITY(); INsert into ChildTable values(@key column3 column4) Hope this is helpful. Mark it answered if it is so.