Is it possible to create more than one table at a time using single create table statement.
5
-
why would you need to do that?sfossen– sfossen2009-03-09 04:51:40 +00:00Commented Mar 9, 2009 at 4:51
-
@Sfossen, Maybe on an application installation where performance is key?alex– alex2009-03-09 04:59:01 +00:00Commented Mar 9, 2009 at 4:59
-
@alex: how does that perform faster?sfossen– sfossen2009-03-09 05:02:38 +00:00Commented Mar 9, 2009 at 5:02
-
@Sfossen: Less queries to the database ? Just a punch in the dark..alex– alex2009-03-09 06:36:36 +00:00Commented Mar 9, 2009 at 6:36
-
@alex: it still has to run the query, that is the heavyweight part, not the sending of the query.sfossen– sfossen2009-03-09 17:19:15 +00:00Commented Mar 9, 2009 at 17:19
Add a comment
|
5 Answers
For MySQL, you can use multi-query to execute multiple SQL statements in a single call. You'd issue two CREATE TABLE statements separated by a semicolon.
But each CREATE TABLE statement individually can create only one table. The syntax supported by MySQL does not allow multiple tables to be created simultaneously.
@bsdfish suggests using transactions, but DDL statements like CREATE TABLE cause implicit transaction commits. There's no way to execute multiple CREATE TABLE statements in a single transaction in MySQL.
I'm also curious why you would need to create two tables simultaneously. The only idea I could come up with is if the two tables have cyclical dependencies, i.e. they reference each other with foreign keys. The solution to that is to create the first table without that foreign key, then create the second table, then add the foreign key to the first table with ALTER TABLE ADD CONSTRAINT. Dropping either table requires a similar process in reverse.
Comments
Not with MS SQL Server. Not sure about mysql.
Can you give more info on why you'd want to do this? Perhaps there's an alternative approach.
Comments
I don't know, but I don't think you can do that. Why you want to do this?
Comments
Not in standard SQL using just the 'CREATE TABLE' statement. However, you can write multiple statements inside a CREATE SCHEMA statement, and some of those statements can be CREATE TABLE statements. Next question - does your DBMS support CREATE SCHEMA? And does it have any untoward side-effects?
Judging from the MySQL manual pages, it does support CREATE SCHEMA as a synonym for CREATE DATABASE. That would be an example of one of the 'untoward side-effects' I was referring to.
(Did you know that standard SQL does not provide a 'CREATE DATABASE' statement?)
answered Mar 9, 2009 at 6:20
Jonathan Leffler
759k145 gold badges961 silver badges1.3k bronze badges
Comments
I don't think it's possible to create more than one table with a 'CREATE TABLE' command. Everything really depends on what you want to do. If you want the creation to be atomic, transactions are probably the way to go. If you create all your tables inside a transaction, it will act as a single create statement from the perspective of anything going on outside the transaction.
