Create table (structure) from existing table

How to create new table which structure should be same as another table

I tried

CREATE TABLE dom AS SELECT * FROM dom1 WHERE 1=2

but its not working error occurred


Try:

Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2

Note that this will not copy indexes, keys, etc.

If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.

If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.


This is what I use to clone a table structure (columns only)...

SELECT TOP 0 *
INTO NewTable
FROM TableStructureIWishToClone

Copy structure only (copy all the columns)

Select Top 0 * into NewTable from OldTable

Copy structure only (copy some columns)

Select Top 0 Col1,Col2,Col3,Col4,Col5 into NewTable from OldTable

Copy structure with data

Select * into NewTable from OldTable

If you already have a table with same structure and you just want to copy data then use this

Insert into NewTable Select * from OldTable

FOR MYSQL:

You can use:

CREATE TABLE foo LIKE bar;

Documentation here.