Best way to create a temp table with same columns and type as a permanent table

Question

I need to create a temp table with same columns and type as a permanent table  What is the best way to do it   The Permanent table has over 100 columns   i e   Usually I create table like this    DECLARE   TT TABLE                  member id INT        reason varchar 1         record status varchar 1          record type varchar 1            But is there any way to do it without mentioning the column names and type  but mention the name of another table with the required columns

User · Answer

This is a MySQL-specific answer  not sure where else it works --  You can create an empty table having the same column definitions with   CREATE TEMPORARY TABLE temp foo LIKE foo    And you can create a populated copy of an existing table with   CREATE TEMPORARY TABLE temp foo SELECT   FROM foo    And the following works in postgres  unfortunately the different RDBMS s don t seem very consistent here   CREATE TEMPORARY TABLE temp foo AS SELECT   FROM foo

User · Answer

Clone Temporary Table Structure to New Physical Table in SQL Server    we will see how to Clone Temporary Table Structure to New Physical Table in SQL Server This is applicable for both Azure SQL db and on-premises   Demo SQL Script  IF OBJECT ID  TempDB   TempTable   IS NOT NULL     DROP TABLE  TempTable   SELECT 1 AS ID  Arul  AS Names INTO  TempTable   SELECT   FROM  TempTable    METHOD 1  SELECT   INTO TempTable1 FROM  TempTable WHERE 1 0   EXEC SP HELP TempTable1      METHOD 2  SELECT TOP 0   INTO TempTable1 FROM  TempTable   EXEC SP HELP TempTable1

User · Answer

select top 0   into  mytemptable from myrealtable

User · Answer

select   into  temptable from tablename where 1 lt  gt 1

User · Answer

I realize this question is extremely old  but for anyone looking for a solution specific to PostgreSQL  it s   CREATE TEMP TABLE tmp table AS SELECT   FROM original table LIMIT 0    Note  the temp table will be put into a schema like pg temp 3   This will create a temporary table that will have all of the columns  without indexes  and without the data  however depending on your needs  you may want to then delete the primary key   ALTER TABLE pg temp 3 tmp table DROP COLUMN primary key    If the original table doesn t have any data in it to begin with  you can leave off the  LIMIT 0

User · Answer

Sortest one     select top 0   into  temptable from mytable   Note   This creates an empty copy of temp  But it doesn t create a primary key

[sql] Best way to create a temp table with same columns and type as a permanent table

Examples related to sql

Examples related to sql-server