Query to insert data into columns and check for pre-existing values for those columns data

Question

I have table t1 and t2 with same column names in both tables, column a and b. I want to enter data of column a and b from t2 into t1 at the same time check weather any data pre exist in t1. If data exist already in a and b column then don't enter that data. The main challenge in my case is , I want to use combination of (a and b) column data to check for pre existing values. Thanks for your precious time in advance.

Answer 1

SQL Server:

INSERT INTO t1 (a,b) 
SELECT a, b 
FROM t2
EXCEPT
SELECT a, b 
FROM t1 

The EXCEPT operator compares the result sets of two queries and eliminates those rows in the first result set that match in the second result set. It uses an exact match to identify and eliminate rows.

There are other options of course, such as MERGE or using NOT EXISTS in your WHERE clause, however, for a simple query such as this EXCEPT will likely perform better.

Answer 2

Mysql

Add Unique key to the combination of fields a and b of table t1

ALTER TABLE `t1` 
ADD UNIQUE INDEX `un_indx` (`a` ASC, `b` ASC);

then insert ignore the data from table t2 to table t1

insert ignore t1(a,b) select a,b from t2;

It will ignore data which violates unique key condition and will insert the rest.