asked
Query help to avoid duplicates
Hi All, I have below tables , which are used for loading data into production table. Could some one please help me write an optimised query(SP). 1)Astage ( AId(pk) Sln, Fid, Snum ) 2)Bstage ( BId(pk), AId (fk), BName, Bamt ) 3) Cstage ( CId(pk), BId (fk), Cname, Camt ) 4) Dstage ( DId(pk), AId (fk), Dname, Damt ) 5) Estage ( EId(pk), AId (fk), Ename, Eamt ) 6) Fstage ( FId(pk), AId(fk), Fname, FAmt ) 7) Gstage ( GId(pk), Fid(fk), Type ) Relation as One to many for A>B>C A>D A>E A>F>G I need to load the data from these tables to production tables of same structure and maintaining the parent child hierarchy With unique I'd generated in each table is reference in child table with foreign key. Thanks, Jithender
Cascade Trigger may be here good solution, A good join for fetching data.
Staging tables (source data extracted and loaded from xml file) 1)Astage ( Astage_Id(pk) Sln, Fid, Snum ) 2)Bstage ( Bstage_Id(pk), Astage_Id (fk), BName, Bamt ) 3) Cstage ( Cstage_Id(pk), Bstage_Id (fk), Cname, Camt ) 4) Dstage ( Dstage_Id(pk), Astage_Id (fk), Dname, Damt ) 5) Estage ( Estage_Id(pk), Astage_Id (fk), Ename, Eamt ) 6) Fstage ( Fstage_Id(pk), Astage_Id(fk), Fname, FAmt ) 7) Gstage ( Gstage_Id(pk), Fstage_id(fk), Type ) Production tables to be loaded with valid data from above staging tables : 1)Aprod ( Aprod_Id(pk) Sln, Fid, Snum ) 2)Bprod ( Bprod_Id(pk), Aprod_Id (fk), BName, Bamt ) 3) Cprod ( Cprod_Id(pk), Bprod_Id (fk), Cname, Camt ) 4) Dprod ( Dprod_Id(pk), Aprod_Id (fk), Dname, Damt ) 5) Eprod ( Eprod_Id(pk), Aprod_Id (fk), Ename, Eamt ) 6) Fprod ( Fprod_Id(pk), Aprod_Id(fk), Fname, FAmt ) 7) Gprod ( Gprod_Id(pk), Fprod_id(fk), Type ) INSERT INTO Cprod ( Bprod_Id (fk), Cname, Camt ) Select BP.Bprod_Id (fk), C.Cname, C.Camt from Cstage C inner join Bstage B ON C.Bstage_Id=B.Bstage_Id INNER JOIN Astage A ON A.Astage_Id=B.Astage_Id INNER JOIN Aprod AP ON AP.Sln=A.Sln AND AP.Fid=A.Fid AND AP.Snum=A.Snum INNER JOIN Bprod BP ON BP.Aprod_Id=AP.Aprod_Id **Cstage** HAVE ONLY 53 RECORDS WHILE THE ABOVE SELECT returns 530 rows. Some one please help me figure out the mistake. Thanks, Jithender
15 People are following this question.
