Member Avatar for vinaii

CREATE TABLE table2
AS
SELECT * FROM databasename.table1

  • 3 Contributors
  • 2 Replies
  • 81 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Infarction

Dani AI

Generated

As observed, that statement produces a new table filled with the rows returned by the SELECT. Important caveats follow: the operation produces a plain data copy only. Column names and types come from the SELECT, but indexes, primary keys, foreign-key constraints, triggers, and column attributes such as AUTO_INCREMENT or DEFAULT are not carried over. The created table will use the server default storage engine unless an engine is specified. See the MySQL manual for exact behavior: CREATE TABLE ... SELECT.

If the goal is a faithful structural copy plus data (indexes preserved), create the empty structure first and then populate it. For example, create an empty table using the structure of the source table and then insert rows using explicit column lists to avoid surprises:

CREATE TABLE new_table LIKE source_table;

INSERT INTO new_table (col1, col2, col3)
SELECT col1, col2, col3 FROM source_table;

To preserve everything that DDL defines (constraints, triggers, engine options), export and reapply the table definition and data with a dump tool rather than a one-step SELECT-based create. For large InnoDB tables, use a transactional dump option to avoid long locks; the mysqldump documentation covers recommended flags and workflows: mysqldump documentation.

As hinted, database-specific details matter; check the MySQL docs for the server version in use and rebuild any indexes or constraints after the copy.

Recommended Answers

All 2 Replies

Member Avatar for Fungus1487

perhaps ask in the correct forum ?

web developement/databases

as the people that may know your answer may not look here.

Member Avatar for Infarction

Why not run the query and see what it does? My guess would be that it basically makes a copy of table1 and saves it to table2.

commented: riiiiiiiiiiiiiiiight! +3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.