Inserting rows into a MySQL table based on a query

Question

I have a query

SELECT A.a, A.b, A.c
FROM A
LEFT JOIN X ON A.a = X.x
WHERE X.x IS NULL

which finds entries in table A which have no corresponding entry in table X. How can I insert the results of this query into table X? If I had only a few entries I would do

INSERT INTO X(xa, xb, xc)
VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2')

(As an aside: the column d is set to auto_increment; does that do the right thing if I run the above? Similarly, columns e, f, ..., h have default values; I trust these will be set accordingly.)

This would not be hard to do by exporting, writing individual SQL snippets for each row in Excel, and executing each; I ask this mainly to learn SQL better.

(Of course my real table has many more fields and various complications, but I think this simplification will suffice.)

Answer 1

INSERT INTO X (xa, xb, xc) 
   SELECT A.a, A.b, A.c
     FROM A
LEFT JOIN X ON A.a = X.x
    WHERE X.x IS NULL

Answer 2

Try this:

INSERT INTO X(xa, xb, xc)
SELECT A.a, A.b, A.c
  FROM A
  LEFT JOIN X ON A.a = X.x
 WHERE X.x IS NULL

Answer 3

You can use INSERT ... SELECT syntax. But you'll have to use a temporary table as you can't insert into a table you join in your select statement.

create temporary table Y like X;
INSERT INTO Y (x,y,z) SELECT a,b,c FROM A LEFT JOIN X ON a=x;
INSERT INTO X SELECT * FROM Y;