question

I am counting the records for a specific project Value (cprj) from the table MatchingRowsOutput-CI-BOM-ProjectCumulative and cross-referencing the number to a DWRowCount value in another table, OrderEntry, for the same project 'cprj'.

If these values match, I would like to take all the records for that project (cprj) from the MatchingRowsOutput-CI-BOM-ProjectCumulative table and output them into another table called CI-BM-CPRJ-Transfer.

I have tried many different methods, trying to use CASE statements, WHERE statements, HAVING statements but none of these have worked.

The code pasted below is the most successful that I can achieve, returning 0 rows affected. I know for a fact there should be a return of 26 rows for the TEK#7000 cprj.

Also, after I have pulled the records from the dbo.MatchingRowsOutput-CI-BOM-ProjectCumulative table and placed into the dbo.[CI-BM-CPRJ-Transfer] table I would like to delete the records that was used for the reference

out of the dbo.OrderEntry table. I have not tried to code that part yet in the code below.

Any help is much appreciated.

Insertinto SSIS.dbo.[CI-BM-CPRJ-Transfer](cprj, item, PartNumber, SWITEM, QTY, rutm, trid, mitm, pono, sitm, opol, qana, scpf, cwar, opno, cpha, exin, itlu, ssta, dsca, dscb, dscd, suno, ctyo, eitm)SELECT 
    PartList.[cprj],
    XMLLines.[item],
    XMLLines.[PartNumber],
    XMLLines.[SWITEM],
    XMLLines.[QTY],
    XMLLines.[rutm],
    XMLLines.[trid],
    XMLLines.[mitm],
    XMLLines.[pono],
    XMLLines.[sitm],
    XMLLines.[opol],
    XMLLines.[qana],
    XMLLines.[scpf],
    XMLLines.[cwar],
    XMLLines.[opno],
    XMLLines.[cpha],
    XMLLines.[exin],
    XMLLines.[itlu],
    XMLLines.[ssta],
    XMLLines.[dsca],
    XMLLines.[dscb],
    XMLLines.[dscd],
    XMLLines.[suno],
    XMLLines.[ctyo],
    XMLLines.[eitm]FROM 
    OrderEntry.dbo.OrderEntry as PartList
INNERJOIN
SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative]AS XMLLines ON 
XMLLines.cprj = PartList.cprj
    (SELECT cprj FROM SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative])GroupBy XMLLines.cprj, PartList.DWRowCount, XMLLines.[item], XMLLines.[PartNumber], XMLLines.[SWITEM], XMLLines.[QTY], XMLLines.[rutm], XMLLines.[trid], XMLLines.[mitm], XMLLines.[pono], XMLLines.[sitm],
XMLLines.[opol], XMLLines.[qana], XMLLines.[scpf], XMLLines.[cwar], XMLLines.[opno], XMLLines.[cpha], XMLLines.[exin], XMLLines.[itlu], XMLLines.[ssta], XMLLines.[dsca], XMLLines.[dscb], XMLLines.[dscd],
XMLLines.[suno], XMLLines.[ctyo], XMLLines.[eitm], PartList.[cprj]

Having count(XMLLines.cprj)= PartList.DWRowCount