4 Replies Latest reply: Nov 23, 2012 4:50 AM by Purvesh K RSS

    how to find duplicate entries

    945922
      Hi

      I have table A contains 5 colums and doent contain any constraints and 1000 records are avaible
      I have table B contains 5 columns contain 500 records

      and table A and table B have same data types of columns

      now i want to insert table B rows to table A . If table A contain same rows of table B, should not insert table B rows.

      Please give me solution for with out using any contraints.
        • 1. Re: how to find duplicate entries
          Manik
          merge will help you
          when matched clause can be SKIPPED.
          when not matched then insert....


          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

          Cheers,
          Manik.
          • 2. Re: how to find duplicate entries
            AlbertoFaenza
            Hi,

            Please next time post sample data.
            Please read SQL and PL/SQL FAQ

            If table structures are the same you can do as follow:
            INSERT INTO table_a a
            SELECT * FROM table_b
            MINUS
            SELECT * from table_a;
            Regards.
            Al
            • 3. Re: how to find duplicate entries
              971895
              Use minus
              • 4. Re: how to find duplicate entries
                Purvesh K
                942919 wrote:
                Hi

                I have table A contains 5 colums and doent contain any constraints and 1000 records are avaible
                I have table B contains 5 columns contain 500 records

                and table A and table B have same data types of columns

                now i want to insert table B rows to table A . If table A contain same rows of table B, should not insert table B rows.

                Please give me solution for with out using any contraints.
                Not sure why you despise constraints; But below could be a way (IMAGINARY & Un-Tested) since you chose not to provide us with Sample Tables and Data
                INSERT INTO table_a
                select col1, col2... coln
                  from table_b b
                 where not exists ( select 'x' from tabel_a a where a.matching_col = b.matching_col ) -- Your Matching Cols may increase, so adapt accordingly.
                Edited by: Purvesh K on Nov 23, 2012 4:20 PM