Check Whether Set Of Rows Exists In Production

I have two similar tables, one in Production and another in Work in Progress. I need to copy set of records from Work in Progress to Production. If similar set of records already t

Solution 1:

One strategy (SQL2005 and later):

select a,b,c from InProgress
EXCEPT
select a,b,c from Production

Or:

select a,b,c, min(criteriaID), max(criteriaID)
from (
    SELECT criteriaID, a,b,c from InProgress
    UNIONALLSELECT criteriaID, a,b,c from Production
) t groupby a,b,c havingmin(criteriaID) <>max(criteriaID)

Solution 2:

This sort of syntax usually gets the job done.

insertinto table1
(field1, field2, etc)
from table2
wherenotexists
(subquery tocheckfor exsiting records)

Solution 3:

Here is the query I used to solve this.

        SELECT CriteriaId FROM CRITERIA JBCRV1, (
            SELECT DISTINCT JBCRV2.CriteriaId AS CRITERIAID 
            FROM WIPCRITERIA JWBCRV1 
            INNER JOIN CRITERIA JBCRV2
                ON    JWBCRV1.CriteriaVal= JBCRV2.CriteriaVal
                AND   JWBCRV1.CriteriaText = JBCRV2.CriteriaText
            WHERE  JWBCRV1.CriteriaId = #{CriteriaId}
            GROUP BY JBCRV2.CriteriaId HAVING COUNT(1) = 
                (SELECT COUNT(1) 
                 FROM WIPCRITERIA 
                 WHERE CriteriaId = #{CriteriaId} ) 
        ) RESULT_TABLE
        WHERE JBCRV1.CriteriaId = RESULT_TABLE.CRITERIAID
        GROUP BY JBCRV1.BEN_CRTR_ID HAVING COUNT(1) = 
                (SELECT COUNT(1) 
                 FROM WIPCRITERIA 
                 WHERE CriteriaId = #{CriteriaId} )

I have tested for various conditions & this work.

Thanks everyone who tried to help me.