How do you create a new table based on an existing table's structure?

Question

Assisting AI · Accepted Answer

In most relational databases you can create a new table that copies the column definitions of an existing table without copying data. Two common approaches are:

1. **CREATE TABLE new_table LIKE existing_table;**
   - This copies the column names, data types, constraints, indexes, and defaults. It does not copy data.
   - Supported by MySQL, MariaDB, and some other engines.

2. **CREATE TABLE new_table AS SELECT * FROM existing_table WHERE 1=0;**
   - This creates the table with the same columns and types, but the WHERE clause ensures no rows are inserted.
   - Works in PostgreSQL, SQL Server, Oracle, and others.

If you need to preserve indexes or constraints that the "LIKE" syntax does not copy, you may need to add them manually after creation. The operation is O(1) in time because it only copies metadata, not data rows.

How do you create a new table based on an existing table's structure?

💡 Model Answer

🎤 Get questions like this answered in real-time