Insert data into one table from another table avoiding duplicates

Question

I've got a table as follows

Table1

ID   Name   Tag
-----------------
1    N1     2.1
2    N2     3.5
3    N1     3.5
4    N3     8.1

I create a new table Table2 with ID and Name (unique constraint) and I want to insert Table1's contents into Table2 avoiding duplicates, in the sense that I want only 1, 2 and 4 from Table1 in Table2.

I've tried this but it doesn't seem to work and I get the unique constraint error (ORACLE SQL)

INSERT INTO TABLE2 (ID, NAME)
SELECT ID, NAME
FROM TABLE1
WHERE NAME NOT IN (SELECT NAME FROM TABLE2);

Please can someone point me in the right direction?

Sorry for not making myself clear. Table2 is a brand new table. I want the first values inserted, the following duplicates should be ignored. So in my case, N1, N2 get inserted, N1 is dupe so it is ignored, N3 is inserted

Answer 1

OK - from your description, I understand table t2 is currently empty, and you want to copy the rows where id is in (1, 2, 4) from table t1 to table t2.

Why your code fails:

You seem to believe that the condition is applied to the first row in t1, it passes so it is inserted into t2, then the condition is applied to the second row in t1 (using what is already inserted in t2), etc. - and you don't understand why there is any attempt to insert ALL the rows from t1 into t2. Why doesn't the third row fail the WHERE clause?

Good question! The reason is that operations are done on a SET basis. The WHERE condition uses table t2 AS IT WAS before the INSERT operation began. So for ALL rows, the WHERE clause compares to an empty table t2.

How to fix this... Decide which id you want to add when there are duplicate names. For example, one way to get the result you said you wanted is to select MIN(id) for each name. Moreover, you still want to check if the name exists in t2 already (since you may do this again in the future, when t2 is already partially populated).

insert into t2 ( id, name )
    select   min(id), name
    from     t1
    where    name not in (select name from t2)
    group by name
;

Answer 2

You can try it bother....!

Insert into tb2(Field1, Field2)
SELECT Field1, Field2
FROM tb1
WHERE NOT EXISTS (SELECT Field1 FROM tb1) ;

Answer 3

This is how I understood the question:

SQL> create table table2
  2    (id   number,
  3     name varchar2(2),
  4     tag  number,
  5          constraint pk_t2 primary key (id, name)
  6    );

Table created.

SQL>
SQL> insert into table2 (id, name, tag)
  2  with test (id, name, tag) as
  3  (select 1, 'N1', 2.1 from dual union
  4   select 2, 'N2', 3.5 from dual union
  5   select 3, 'N1', 3.5 from dual union
  6   select 4, 'N3', 8.1 from dual
  7  )
  8  select min(id), name, max(tag)
  9  from test
 10  group by name;

3 rows created.

SQL>
SQL> select * from table2 order by id;

        ID NA        TAG
---------- -- ----------
         1 N1        3,5
         2 N2        3,5
         4 N3        8,1

SQL>

Answer 4

Try to check if the id and name from Table1 is doesn't exist in Table2, if then insert.

If the unique constraint on TABLE2 is a composite key then run this:

INSERT INTO TABLE2 (ID, NAME)
SELECT A.ID, A.NAME
FROM TABLE1 A
WHERE NOT EXISTS (SELECT NULL FROM TABLE2 B WHERE A.ID=B.ID AND A.NAME=B.NAME);

If there are two unique constraints; one on the id, and the other on the name then run this instead:

INSERT INTO TABLE2 (ID, NAME)
SELECT A.ID, A.NAME
FROM TABLE1 A
WHERE NOT EXISTS (SELECT NULL FROM TABLE2 B WHERE A.ID=B.ID OR A.NAME=B.NAME);

Answer 5

ORACLE, in case you need to get values from 2 different tables.

below example,i use an increment case.

INSERT INTO TABLE1 
(INDEX, REMARKS, NAME, AGE)
(SELECT (SELECT colescs(MAX(INDEX),0) FROM TABLE1)+1,
'any remarks',
t2.NAME, t2,age from TABLE2 t2 where t2.name = 'apple')

explanation match below numbers (1)-(1), (2)-(2) ...

• INSERT INTO TABLE1
• (INDEX, //index increment (1)
• REMARKS, //hard code (2)
• NAME, //from table2 (3)
• AGE) //from table2 (4)
• (SELECT // this part is to get values from another table
• (SELECT colescs(MAX(INDEX),0) FROM TABLE1)+1, //increment (1)
• 'any remarks', //hard code value (2)
• t2.NAME, //from table2 (3)
• t2,age //from table2 (4)
• from TABLE2 t2 where t2.name = 'apple') //condition for table2

Answer 6

When we need to unique any two or more column we have to create unique index.

Run this query

ALTER TABLE TABLE2 ADD UNIQUE unique_index( id, name);

and then INSERT INTO TABLE2 (id,name,tag) VALUES(1, "N1", 3.5 ) ON DUPLICATE KEY UPDATE tag=3.5

this will also help to update new tag