Ways to insert a new table after a combination of data in multiple tables

Take MySQL for example.

The first thing to think of when encountering this problem is to first query the corresponding data from each table and then use the program to iterate through the assembly and insert it into the database. This is the most intuitive solution, but there are problems with this approach when dealing with very large result sets, such as: exceeding memory limits, running too long, etc...

It would be helpful to use the MySQL on duplicate key Update method to assemble the data in steps.

There are now 3 table a,b,c. The structure of each table is as follows:

A table contains fields: Id,a,b,c;

B table contains fields: aid,d,e,f; field AID corresponds to the ID in table a

C table contains fields: aid,a,b,c,d,e,f; field AID corresponds to the ID in table a

Now to combine the data in table A and table B into the C table, where both A and b contain 1000w+ data, and if the query-"assemble-" is inserted into the database, the query steps and assembly steps will exceed the memory limit.

This is possible with the following steps:

1. The
2. is inserted into C after querying the qualifying data from a. For example:

    insert  into  C (C.AID,C.A,C.B, C.C) select  a.id,a.a,a.b,a.c from  a where  a.id<   5000000  

   Execution completes approximately 40 seconds

4. The data that matches the criteria is queried from table B and inserted into C. For example:

   INSERT  into SELECT  from WHERE b.aid<5000000onKEYUPDATE c.d=  Values(C.D), C.E=values(C.E), c.f=values(C.F)

   Approximately more than 50 seconds to complete execution

This inserts the data combination in a and B into C.

Ways to insert a new table after a combination of data in multiple tables