HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

MySQL: Create multiple tables with one query

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablescreatewithquerymysqlonemultiple

Problem

Can you list multiple table names in a MySQL CREATE TABLE query?

CREATE TABLE table1, table2, table3 LIKE table_template;


If so, can you do it with the IF NOT EXISTS clause?

CREATE TABLE IF NOT EXISTS table1, table2, table3 LIKE table_template;


I know you can do this with DROP TABLE:

DROP TABLE IF EXISTS table1, table2, table3;


The docs for DROP TABLE explicitly show that you enter a string of names:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]


The docs for CREATE TABLE don't show a table name string:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

More Syntax Variations ...


Can anyone point me to a reference that states explicitly that multiple tables can or can't be created with one query?

I searched the CREATE TABLE Syntax doc for "string", "tbl_name", and "names", but no luck.

Solution

Looking at the docs, it looks like you can't create multiple tables with one CREATE. You can use IF NOT EXISTS and LIKE together, like this:

CREATE TABLE IF NOT EXISTS table1 LIKE table_template;
CREATE TABLE IF NOT EXISTS table2 LIKE table_template;
CREATE TABLE IF NOT EXISTS table3 LIKE table_template;


Here's the page from the MySQL docs: CREATE TABLE

Code Snippets

CREATE TABLE IF NOT EXISTS table1 LIKE table_template;
CREATE TABLE IF NOT EXISTS table2 LIKE table_template;
CREATE TABLE IF NOT EXISTS table3 LIKE table_template;

Context

StackExchange Database Administrators Q#36069, answer score: 7

Revisions (0)

No revisions yet.