Copy Table Structure into New Table

Create table (structure) from existing table

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.

Create clone copy of table without data in SQL Server

Copy all columns from selected table

Select Top 0 * into NewTable from OldTable

Copy some columns from selected table

Select Top 0 Col1,Col2 into NewTable from OldTable

Copy all(Data and Structure)

Select * into NewTable from OldTable

Create Table by Copying Structure of Existing Table

like this, however this will not create indexes and constraints

select * into Drop_Centers_Detail
from Centers_Detail
where 1 = 0

Copy table without copying data

Try:

CREATE TABLE foo SELECT * FROM bar LIMIT 0

Or:

CREATE TABLE foo SELECT * FROM bar WHERE 1=0

Copy data into another table

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

Copy table structure to new table in sqlite3

You could use a command like this:

CREATE TABLE copied AS SELECT * FROM mytable WHERE 0

but due to SQLite's dynamic typing, most type information would be lost.

If you need just a table that behaves like the original, i.e., has the same number and names of columns, and can store the same values, this is enough.

If you really need the type information exactly like the original, you can read the original SQL CREATE TABLE statement from the sqlite_master table, like this:

SELECT sql FROM sqlite_master WHERE type='table' AND name='mytable'

How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

• sequences
• triggers
• indexes
• some constraints may not be copied
• materialized view logs

This also does not handle partitions

---

---

Related Topics