question

Jithender avatar image
Jithender 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
tsqlsql server 2008 r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bhanupratapsngh9 avatar image
bhanupratapsngh9 answered
Cascade Trigger may be here good solution, A good join for fetching data.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jithender avatar image
Jithender answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I take it that Bstage has fewer rows per Bstage_Id than Cstage and likewise with Astage and Bstage. How many rows in Aprod and Bprod? You've joined them in, so will need to account for any matching rows.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.