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

very helpful, intriguing to have a where clause that is always false! — JosephDoggie, Aug 09 '17 at 14:09

Kevin Crowell · Accepted Answer · 2010-03-24T06:35:12.420

208

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.

edited Mar 24 '10 at 06:35

answered Mar 24 '10 at 06:23

Kevin Crowell

10,082
4
35
51

1

Kevin, just a small formatting change in your answer:- Select * Into From Where 1 = 2 – Ashish Gupta Mar 24 '10 at 06:32
6

Qutbuddin, 1=2 will prevent data copying from source to destination table. Try yourself:- CREATE TABLE Table1 ( Id int , Name varchar(200) ) INSERT INTO table1 VALUES (1,'A') INSERT INTO table1 VALUES(2,'B') -- Will create table2 with data in table1 SELECT * INTO Table2 FROM Table1 WHERE 1=2 -- Will create table2 without data in table1 SELECT * INTO Table2 FROM Table1 WHERE 1=2 – Ashish Gupta Mar 24 '10 at 06:57
1

I thought 1=2 would be just a weird wrong argument in order to avoid copying data. – Arthur Zennig Sep 28 '16 at 10:21
1

also possible `select top 0 * into dest_table from src_table` – Branko Mar 19 '22 at 16:43
this query would still copy over the restrictions for columns, like "identity" – maha Aug 11 '23 at 10:02

score 74 · Answer 2 · answered Aug 15 '12 at 15:17

74

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

SELECT TOP 0 *
INTO NewTable
FROM TableStructureIWishToClone

answered Aug 15 '12 at 15:17

DanielM

939
6
4

4

This solution is clearer than having the extra condition "1 = 2", I would recommend this – Pinte Dani Oct 02 '19 at 05:58
This gives me an error, 0 is not valid at this position. Any idea ? – Abhishek Mittal Jul 13 '23 at 14:31

Abhishek Maurya · Answer 3 · 2015-12-10T10:10:22.367

50

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

edited Dec 10 '15 at 10:10

answered Dec 10 '15 at 10:04

Abhishek Maurya

519
5
8

1

Worked for me in MSSQL 2008 R2 – Yoseph Mar 29 '17 at 14:28
1

Great solution, simple and elegant. Is there a hack to make this copy indexes and primary keys as well? – Tumaini Mosha Jun 20 '17 at 20:22
is it a shallow copy or a deep copy – Himanshu Poddar Sep 13 '21 at 05:31
Thank you!! I like how you provided options. Very helpful! – Leo Gurdian Apr 29 '22 at 21:10

score 32 · Answer 4 · edited Apr 08 '18 at 11:34

32

FOR MYSQL:

You can use:

CREATE TABLE foo LIKE bar;

Documentation here.

edited Apr 08 '18 at 11:34

Mohammad Yusuf

16,554
10
50
78

answered May 18 '15 at 19:35

Francesco Gusmeroli

633
6
18

27

The question is tagged as `sql-server` for which this syntax isn't valid, fyi. – Molomby Jun 25 '15 at 02:55
Shouldn't count as answer due to relation to MySQL not to sql-server – celerno Oct 21 '16 at 16:49
7

FYI - this also keeps primary keys and indexes, retained. – garg10may Oct 18 '19 at 07:50

score 16 · Answer 5 · edited Aug 05 '13 at 09:19

16

Create table abc select * from def limit 0;

This will definite work

edited Aug 05 '13 at 09:19

kleopatra

51,061
28
99
211

answered Aug 05 '13 at 09:15

GIRDHAR SINGH

169
1
2

Perfect! Thank you – Dylan B Jun 26 '19 at 07:10
3

Notice that the question is tagged with `sql-server`, and this does **not** work in SQL Server. – Alexandre Oct 07 '20 at 22:10

score 12 · Answer 6 · answered Aug 27 '13 at 08:24

12

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

answered Aug 27 '13 at 08:24

JsonStatham

9,770
27
100
181

This works well and creates all the constraints etc. – ITA Dec 02 '22 at 16:50

score 7 · Answer 7 · answered Jul 15 '16 at 22:09

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(*) > 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 > 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) > 0
                SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

            IF LEN(@IncludedColumns) > 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')) >= -1
        BEGIN
         DEALLOCATE indexcursor
        END

        IF (SELECT CURSOR_STATUS('global','const_cursor')) >= -1
        BEGIN
         DEALLOCATE const_cursor
        END


        PRINT 'Error Message: ' + ERROR_MESSAGE(); 
    END CATCH

END

GO

Making this faster might be as simple as declaring your cursors as `CURSOR LOCAL FAST_FORWARD`. Personally I'm attempting to create a similar script without using cursors and see how that performs. — mendosi, Apr 06 '17 at 07:19
Hi @mendosi I know it's old, but I'm currently looking into generating CREATE script with all the misc things (constraints/indexes/partitions/triggers/etc) along with column definition. I was wondering if you had any success recreating this with a non-cursor approach. if so, would you mind sharing it? Much appreciated, thank you — 007, May 13 '20 at 20:18
The script I wrote copies one or more tables and doesn't use a cursor. It's also too big for a comment. Instead I'll link to Hans Michiels script: https://www.hansmichiels.com/2016/02/18/how-to-copy-a-database-table-structure-t-sql-scripting-series-s01e01/ — mendosi, May 15 '20 at 00:11

score 5 · Answer 8 · answered Jan 02 '15 at 12:47

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 ;

Adrian Fâciu · Answer 9 · 2010-03-24T06:55:46.823

4

I don't know why you want to do that, but try:

SELECT *
INTO NewTable
FROM OldTable
WHERE 1 = 2

It should work.

edited Mar 24 '10 at 06:55

answered Mar 24 '10 at 06:28

Adrian Fâciu

12,414
3
53
68

I think that would also copy the data? he wants only the structure. – Ashish Gupta Mar 24 '10 at 06:32
@Ashis Gupta - Thanks, i've forgot the "where" :) – Adrian Fâciu Mar 24 '10 at 06:56

score 4 · Answer 10 · edited May 13 '16 at 05:54

4

If you Want to copy Same DataBase

Select * INTO NewTableName from OldTableName

If Another DataBase

Select * INTO NewTableName from DatabaseName.OldTableName

edited May 13 '16 at 05:54

Iamat8

3,888
9
25
35

answered May 13 '16 at 05:51

cd pandey

41
1

score 3 · Answer 11 · answered Feb 13 '14 at 12:02

3

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;

answered Feb 13 '14 at 12:02

NameNotFoundException

876
14
31

3

this does not copy constraints and keys – Trikaldarshiii Aug 21 '14 at 09:42

score 3 · Answer 12 · answered Mar 24 '10 at 06:25

3

SELECT * 
INTO NewTable
FROM OldTable
WHERE 1 = 2

answered Mar 24 '10 at 06:25

Chris Latta

20,316
4
62
70

score 3 · Answer 13 · edited Jul 25 '17 at 10:49

3

SELECT * INTO newtable
from Oldtable

edited Jul 25 '17 at 10:49

Musakkhir Sayyed

7,012
13
42
65

answered Aug 20 '16 at 09:48

BalaRam

59
7

1

Please use code markup for greater readability,also that's more useful to explain a little about your code. – Nima Derakhshanjan Aug 20 '16 at 10:11
2

Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. In particular, it looks to the untrained eye as if this would also copy the contents of `Oldtable`. How is that avoided? – Toby Speight Jul 25 '17 at 13:46

score 3 · Answer 14 · answered Mar 22 '22 at 19:12

According to How to Clone Tables in SQL, it is:

CREATE TABLE copyTable LIKE originalTable;

That works for just the sructure. For the structure and the data use this:

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

user_v · Answer 15 · 2013-08-27T08:13:40.367

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;

score 1 · Answer 16 · answered Sep 09 '20 at 10:51

1

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 <tablename> as select * from <sourcetablename> where 1>2;

By this false condition you can leave the records and copy the structure.

answered Sep 09 '20 at 10:51

Sai Durga Kamesh Kota

169
1
4

1

This is a duplicate of existing answers. Read through existing answers before submitting a new one, and add comments/votes if appropriate. – Kevin Hogg Sep 09 '20 at 13:30
1

But it is not same as that of existing answer here i used create command to do this action – Sai Durga Kamesh Kota Sep 10 '20 at 04:12
2

If you review @AbhiUrs answer (02-Jan-2015), your answer is similar to the first part of their answer, albeit with a slightly different where clause. First part => `create table AT_QUOTE_CART as select * from QUOTE_CART where 0=1 ;` Replace those tablenames and we get: `create table as select * from where 0=1 ;` As for the where clause, `0=1` achieves the same result as `1>2` which is no data retrieved. – Kevin Hogg Sep 11 '20 at 09:25

score 0 · Answer 17 · answered Feb 09 '21 at 09:38

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)

Create table (structure) from existing table

17 Answers17

Linked

Related