Create table structure from existing table

Question

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

User · Answer

Copy the table structure - select   into newtable from oldtable where 1 2   Copy the table structure along with table data - select   into newtable from oldtable where 1 1

User · Answer

If you Want to copy Same DataBase  Select   INTO NewTableName from OldTableName  If Another DataBase  Select   INTO NewTableName from DatabaseName OldTableName

User · Answer

If you want to create a table with the only structure to be copied from the original table then you can use the following command to do that  create table  lt tablename gt  as select   from  lt sourcetablename gt  where 1 gt 2    By this false condition you can leave the records and copy the structure

User · Answer

SELECT    INTO NewTable FROM OldTable WHERE 1   2

User · Answer

try this   the below one copy the entire structure of the existing table but not the data   create table AT QUOTE CART as select   from QUOTE CART where 0 1     if you want to copy the data then use the below one   create table AT QUOTE CART as select   from QUOTE CART

User · Answer

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

User · Answer

I don t know why you want to do that  but try   SELECT   INTO NewTable FROM OldTable WHERE 1   2   It should work

User · Answer

Found here what I was looking for  Helped me recall what I used 3-4 years back   I wanted to reuse the same syntax to be able to create table with data resulting from the join of a table   Came up with below query after a few attempts   SELECT a   INTO   DetailsArchive FROM    SELECT d           FROM   details AS d                INNER JOIN                port AS p                ON p importid   d importid         WHERE  p status   2  AS a

User · Answer

Create table abc select   from def limit 0    This will definite work

User · Answer

I use the following stored proc for copying a table s schema  including PK  indexes  partition status   It s not very swift  but seems to do the job   I I welcome any ideas how to speed it up                  Clones a table s schema from an existing table  without data          if target table exists  it will be dropped first          The following schema elements are cloned                Structure               Primary key               Indexes               Constraints     DOES NOT copy            Triggers           File groups      ASSUMPTION  constraints are uniquely named with the table name  so that we dont end up with duplicate constraint names    CREATE PROCEDURE  dbo   spCloneTableStructure    SourceTable            nvarchar 255    DestinationTable       nvarchar 255    PartionField           nvarchar 255    SourceSchema           nvarchar 255     dbo      DestinationSchema      nvarchar 255     dbo        RecreateIfExists       bit   1  AS BEGIN  DECLARE  msg  nvarchar 200    PartionScript nvarchar 255    sql NVARCHAR MAX       IF EXISTS Select s name As SchemaName  t name As TableName                         From sys tables t                         Inner Join sys schemas s On t schema id   s schema id                         Inner Join sys partitions p on p object id   t object id                         Where p index id In  0  1  and t name    SourceTable                         Group By s name  t name                         Having Count     gt  1           SET  PartionScript     ON  PS PartitionByCompanyId        PartionField            else         SET  PartionScript       SET NOCOUNT ON  BEGIN TRY        SET  msg     CloneTable       DestinationTable     - Step 1  Drop table if exists  Timestamp       CONVERT NVARCHAR 50  GETDATE   108       RAISERROR   msg 0 1  WITH NOWAIT     --drop the table     if EXISTS  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE NAME    DestinationTable      BEGIN         if  RecreateIfExists   1             BEGIN                 exec  DROP TABLE       DestinationSchema            DestinationTable                    END         ELSE             RETURN     END      SET  msg     CloneTable       DestinationTable     - Step 2  Create table  Timestamp       CONVERT NVARCHAR 50  GETDATE   108      RAISERROR   msg 0 1  WITH NOWAIT     --create the table     exec  SELECT TOP  0    INTO       DestinationTable      FROM       SourceTable                    --create primary key     SET  msg     CloneTable       DestinationTable     - Step 3  Create primary key  Timestamp       CONVERT NVARCHAR 50  GETDATE   108      RAISERROR   msg 0 1  WITH NOWAIT     DECLARE  PKSchema nvarchar 255    PKName nvarchar 255   count   INT     SELECT TOP 1  PKSchema   CONSTRAINT SCHEMA   PKName   CONSTRAINT NAME FROM INFORMATION SCHEMA TABLE CONSTRAINTS WHERE TABLE SCHEMA    SourceSchema AND TABLE NAME    SourceTable AND CONSTRAINT TYPE    PRIMARY KEY      IF NOT  PKSchema IS NULL AND NOT  PKName IS NULL     BEGIN         DECLARE  PKColumns nvarchar MAX          SET  PKColumns               SELECT  PKColumns    PKColumns         COLUMN NAME                    FROM INFORMATION SCHEMA KEY COLUMN USAGE              where TABLE NAME    SourceTable and TABLE SCHEMA    SourceSchema AND CONSTRAINT SCHEMA    PKSchema AND CONSTRAINT NAME   PKName             ORDER BY ORDINAL POSITION          SET  PKColumns   LEFT  PKColumns  LEN  PKColumns  - 1           exec  ALTER TABLE       DestinationSchema            DestinationTable      ADD  CONSTRAINT  PK      DestinationTable      PRIMARY KEY CLUSTERED       PKColumns          PartionScript       END      --create other indexes     SET  msg     CloneTable       DestinationTable     - Step 4  Create Indexes  Timestamp       CONVERT NVARCHAR 50  GETDATE   108      RAISERROR   msg 0 1  WITH NOWAIT     DECLARE  IndexId int   IndexName nvarchar 255    IsUnique bit   IsUniqueConstraint bit   FilterDefinition nvarchar max    type int      set  count 0     DECLARE indexcursor CURSOR FOR     SELECT index id  name  is unique  is unique constraint  filter definition  type FROM sys indexes WHERE is primary key   0 and object id   object id        SourceSchema            SourceTable            OPEN indexcursor      FETCH NEXT FROM indexcursor INTO  IndexId   IndexName   IsUnique   IsUniqueConstraint   FilterDefinition   type     WHILE   FETCH STATUS   0        BEGIN             set  count   count  1             DECLARE  Unique nvarchar 255              SET  Unique   CASE WHEN  IsUnique   1 THEN   UNIQUE   ELSE    END              DECLARE  KeyColumns nvarchar max    IncludedColumns nvarchar max              SET  KeyColumns                  SET  IncludedColumns                   select  KeyColumns    KeyColumns         c name          CASE WHEN is descending key   1 THEN  DESC  ELSE  ASC  END       from sys index columns ic             inner join sys columns c ON c object id   ic object id and c column id   ic column id             where index id    IndexId and ic object id   object id        SourceSchema            SourceTable        and key ordinal  gt  0             order by index column id              select  IncludedColumns    IncludedColumns         c name        from sys index columns ic             inner join sys columns c ON c object id   ic object id and c column id   ic column id             where index id    IndexId and ic object id   object id        SourceSchema            SourceTable        and key ordinal   0             order by index column id              IF LEN  KeyColumns   gt  0                 SET  KeyColumns   LEFT  KeyColumns  LEN  KeyColumns  - 1               IF LEN  IncludedColumns   gt  0             BEGIN                 SET  IncludedColumns     INCLUDE      LEFT  IncludedColumns  LEN  IncludedColumns  - 1                    END              IF  FilterDefinition IS NULL                 SET  FilterDefinition                  ELSE                 SET  FilterDefinition    WHERE      FilterDefinition                    SET  msg     CloneTable       DestinationTable     - Step 4     CONVERT NVARCHAR 5   count       Create Index      IndexName      Timestamp       CONVERT NVARCHAR 50  GETDATE   108              RAISERROR   msg 0 1  WITH NOWAIT              if  type   2                 SET  sql    CREATE      Unique     NONCLUSTERED INDEX       IndexName      ON       DestinationSchema            DestinationTable            KeyColumns          IncludedColumns    FilterDefinition     PartionScript             ELSE                 BEGIN                     SET  sql    CREATE      Unique     CLUSTERED INDEX       IndexName      ON       DestinationSchema            DestinationTable            KeyColumns          IncludedColumns    FilterDefinition    PartionScript                 END             EXEC   sql              FETCH NEXT FROM indexcursor INTO  IndexId   IndexName   IsUnique   IsUniqueConstraint   FilterDefinition   type        END     CLOSE indexcursor     DEALLOCATE indexcursor      --create constraints     SET  msg     CloneTable       DestinationTable     - Step 5  Create constraints  Timestamp       CONVERT NVARCHAR 50  GETDATE   108      RAISERROR   msg 0 1  WITH NOWAIT     DECLARE  ConstraintName nvarchar max    CheckClause nvarchar max    ColumnName NVARCHAR 255      DECLARE const cursor CURSOR FOR         SELECT             REPLACE dc name   SourceTable   DestinationTable   definition   c name         FROM sys default constraints dc             INNER JOIN sys columns c ON dc parent object id   c object id AND dc parent column id   c column id         WHERE OBJECT NAME parent object id    SourceTable                    OPEN const cursor     FETCH NEXT FROM const cursor INTO  ConstraintName   CheckClause   ColumnName     WHILE   FETCH STATUS   0        BEGIN             exec  ALTER TABLE       DestinationTable      ADD CONSTRAINT       ConstraintName      DEFAULT      CheckClause     FOR      ColumnName              FETCH NEXT FROM const cursor INTO  ConstraintName   CheckClause   ColumnName        END      CLOSE const cursor     DEALLOCATE const cursor                    END TRY     BEGIN CATCH         IF  SELECT CURSOR STATUS  global   indexcursor     gt   -1         BEGIN          DEALLOCATE indexcursor         END          IF  SELECT CURSOR STATUS  global   const cursor     gt   -1         BEGIN          DEALLOCATE const cursor         END           PRINT  Error Message      ERROR MESSAGE         END CATCH  END  GO

User · Answer

Try   Select   Into  lt DestinationTableName gt  From  lt SourceTableName gt  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

User · Answer

FOR MYSQL   You can use   CREATE TABLE foo LIKE bar    Documentation here

User · Answer

Its probably also worth mentioning that you can do the following   Right click the table you want to duplicate   Script Table As   Create To   New Query Editor Window  Then  where is says the name of the table you just right clicked in the script that has been generated  change the name to what ever you want your new table to be called and click Execute

User · Answer

I needed to copy a table from one database another database  For anyone using a GUI like Sequel Ace you can right click table and click  copy create table syntax  and run that query  you can edit the query  e g  change table name  remove foreign keys  add remove columns if desired

User · Answer

This is what I use to clone a table structure  columns only      SELECT TOP 0   INTO NewTable FROM TableStructureIWishToClone

User · Answer

SELECT   INTO newtable from Oldtable

[sql-server] Create table (structure) from existing table

Examples related to sql-server