copying between multiple tables

Question

I have some records in tables A with fields i.e firstname surname, lastname, school, dob

I have another table B with some records and fields i.e firstname, surname, address, club,

I'd like to use the firstname and surname in table B to check if the record exist in table A if it doesnt it should append the record to table A. I would be glad if you can help me with this

Answer 1

This SQL should do this. Basically, insert from table_b, records with firstname and lastname not existing in table_a.

INSERT INTO table_a (firstname, lastname, address, club)
(
  SELECT DISTINCT firstname, lastname, address, club FROM table_b 
  WHERE (firstname, lastname) NOT IN (SELECT firstname, lastname FROM table_a)
)

Answer 2

Try this query -

INSERT INTO table_a(firstname, lastname) 
  SELECT b.firstname, b.lastname FROM table_b b
    LEFT JOIN table_a a ON b.firstname = a.firstname AND b.lastname = a.lastname
  WHERE a.firstname IS NULL AND a.lastname IS NULL;