Re: want to insert all columns while preserving uniqueness on some

Is ilustration_desc the same of cap_text in the select you built?

If it's the same you should try to use this statement:

INSERT INTO ILLUSTRATION
(COUNTRY_CODE, CATALOG_CODE, ART_ID, FIRST_YEAR, LAST_YEAR,
COUNTRY_LANG,ILUSTRATION_DESC)
  SELECT COUNTRY_CODE,

         CATALOG,
         ART_ID,
         FIRST_YEAR,
         LAST_YEAR,
         'EN',
         CAPTEXT
    FROM (SELECT DISTINCT CL.COUNTRY_CODE COUNTRY_CODE,
                          B.CATALOG       CATALOG,
                          A.ART_ID        ART_ID,
                          B.FIRST_YEAR    FIRST_YEAR,
                          B.LAST_YEAR     FAST_YEAR,
                          B.CAPTEXT       CAPTEXT
          --'EN'
            FROM ART A,
                 ARCHCAT_CAPTION B,
                 CATALOG C,
                 (SELECT DISTINCT COUNTRY_CODE
                    FROM COUNTRY_LANGUAGE
                   WHERE COUNTRY_CODE IN ('US', 'CA')) CL
           WHERE RTRIM(RPAD(A.ART_NBR, 8, ' ') || A.ART_SUFFIX) =
                 RTRIM(B.ART_NUM)
             AND B.CATALOG = C.CATALOG_CODE
          MINUS (SELECT COUNTRY_CODE,
                       CATALOG_CODE,
                       ART_ID,
                       TO_CHAR(FIRST_YEAR),
                       TO_CHAR(LAST_YEAR),
                       ILUSTRATION_DESC
                --COUNTRY_LANG
                  FROM ILLUSTRATION
                 WHERE COUNTRY_LANG = 'EN')));

if it's not the same you should try this one:

INSERT INTO ILLUSTRATION
(COUNTRY_CODE,

   CATALOG_CODE,
   ART_ID,
   FIRST_YEAR,
   LAST_YEAR,
   COUNTRY_LANG,
   ILUSTRATION_DESC)
(SELECT COUNTRY_CODE,

          CATALOG,
          ART_ID,
          FIRST_YEAR,
          LAST_YEAR,
          LANG,
          CAPTEXT
     FROM (SELECT DISTINCT CL.COUNTRY_CODE COUNTRY_CODE,
                           B.CATALOG CATALOG,
                           A.ART_ID ART_ID,
                           B.FIRST_YEAR FIRST_YEAR,
                           B.LAST_YEAR,
                           'EN' LANG,
                           B.CAPTEXT CAPTEXT
             FROM ART A,
                  ARCHCAT_CAPTION B,
                  CATALOG C,
                  (SELECT DISTINCT COUNTRY_CODE
                     FROM COUNTRY_LANGUAGE
                    WHERE COUNTRY_CODE IN ('US', 'CA')) CL
            WHERE RTRIM(RPAD(A.ART_NBR, 8, ' ') || A.ART_SUFFIX) =
                  RTRIM(B.ART_NUM)
              AND B.CATALOG = C.CATALOG_CODE) D,
          ILLUSTRATION E
    WHERE D.COUNTRY_CODE = E.COUNTRY_CODE(+)
      AND D.CATALOG_CODE = E.CATALOG_CODE(+)
      AND D.ART_ID = E.ART_ID(+)
      AND D.FIRST_YEAR = TO_CHAR(E.FIRST_YEAR(+))
      AND D.LAST_YEAR = TO_CHAR(E.LAST_YEAR(+)),
      AND D.COUNTRY_LANG = E.CONTRY_LANG
      AND 'EN' = E.COUNTRY_LANG(+)
      AND E.CATALOG_CODE IS NULL);