How to insert into table a new row then do nothing if already exists(without UNIQUE key)

Question

Let's say I have these two tables. Where I insert employees to employee table coming from the staging table.

staging table:

id	employee_id	name
1	12	Paul
2	13	Kyle

employee table

id	employee_id	name
5	4	Will
6	13	Kyle

Now, on the employee table let's say I'd like to copy what's on my staging table currently, using the INSERT SELECT INTO statement, Paul will be inserted but I don't want Kyle to be inserted since he's on the employee table already(employee.employee_id is the defining column).

I know this could be done by just setting a unique or primary key, on employee_id and just using the statement ON DUPLICATE KEY UPDATE then do nothing by just setting them back to their original values.

I'm new to SQL, and I'm stuck with the solution setting a UNIQUE key and ON DUPLICATE KEY UPDATE statement, but I'd like to know how to do this without that solution I mentioned?

Answer 1

First of all, you should keep in mind that the decision whether to create unique or primary keys or not does not depend on how to create insert statements or such. It's a matter of what your table should do and what not. In order to achieve your goal, you can add a where to your insert statement which excludes the already existing entries, as example:

INSERT INTO employees (id, employee_id, name) 
SELECT id, employee_id, name 
FROM staging 
WHERE employee_id NOT IN (SELECT employee_id FROM employees)

Answer 2

Break the problem down into its constituent parts

1. get a list of employees who are in the staging table but not in the target table
2. insert those records only

Part 1 can be achieved with a Left Join and Where condition that the target table is null. Wrap that up as a CTE and then use it in 2)