Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
It's not possible to create multiple tables in a single query... Still not sure why you need more than one table... Using 1=2 makes sure that no rows are returned (this would actually copy content of table1 to table2 otherwise). Code will be the same when creating more than one table. –  Karthick Jul 17 at 10:07

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
Everything in your answer is in my question. I asked if anyone could point me to a reference that explicitly states whether or not multiple tables can be created with one query. –  codewaggle Mar 6 '13 at 18:55
    
Sorry, I missed that when I read the question. –  Dave Johnson Mar 6 '13 at 19:00
    
Thanks for trying, I expect that someone will find the info you posted useful when searching for an answer. –  codewaggle Mar 6 '13 at 19:08
    
@codewaggle The reference you are looking for is in the CREATE TABLE syntax (that you have linked in your question.) It states CREATE [TEMPORARY] TABLE [IF NOT EXISTS] **tbl_name** (create_definition,...) [table_options] [partition_options] That's only one tbl_name, not a list of tables. –  ypercube Jul 17 at 10:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.