CODEWITHSUNDEEP
mysqlsqldatabase
George Dimitriadis
George Dimitriadis

Reputation: 1805

Run multiple inserts with the data of the same select query in MySQL

I am migrating a database from one project to another and the tables don't have the same format, so I have to format the data properly.

Here is a couple of queries example:

INSERT INTO db1.first (a,b,c)
SELECT    x.a, z.b, x.c
FROM      db2.t2 x, db2.t3 z
WHERE     x.id = z.id

INSERT INTO db1.second (q,w)
SELECT    x.d, z.e
FROM      db2.t2 x, db2.t3 z
WHERE     x.id = z.id

What happens here is that I am using the same SELECT query (which in my actual case is much uglier than this example) in order to import some data. Only difference is that I don't use all the columns for every insert, but the rows fetched are exactly the same (the conditions applied are the same just like in the example).

As you can clearly see, this has lead in a lot of copy-pasting and inconvenience, is there a way to run multiple inserts into different tables without copy-pasting my select query like that ?

Thanks in advance !

Upvotes: 0

Views: 154

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You could try creating a temporary table for that session:

CREATE TEMPORARY TABLE IF NOT EXISTS temp AS
SELECT
    x.a, z.b, x.c
FROM
    db2.t2 x
INNER JOIN db2.t3 z
    ON x.id = z.id

And then use this table for both inserts:

INSERT INTO db1.first (a,b,c)
SELECT a, b, c FROM temp

INSERT INTO db1.second (q,w)
SELECT a, b FROM temp

Note that I also replaced your implicit table join with an explicit one using INNER JOIN with an ON clause. This is the generally accepted way of doing joins currently.

If you were using another database, such as SQL Server or Oracle, the best approach might be to use a common table expression (CTE) for the common query used for inserting. But since MySQL doesn't have support for CTE, using a temp table is an alternative.

Note that temporary tables in MySQL only exist for that session, and will be automatically deleted when the session ends. So there is no need to drop it explicitly, unless you want to remove it within the same session where you created it.

Upvotes: 2

Related Questions