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.

Create table abc select * from def limit 0;

This will definite work

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

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

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 ;

1. If you Want to copy Same DataBase

    Select * INTO NewTableName from OldTableName
   

2. If Another DataBase

    Select * INTO NewTableName from DatabaseName.OldTableName
   

SELECT * 
INTO NewTable
FROM OldTable
WHERE 1 = 2

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

SELECT *
INTO NewTable
FROM OldTable
WHERE 1 = 2

It should work.

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;

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;

SELECT * INTO newtable
from Oldtable

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.

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)

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;