Create table variable with exact same structure of another table

Question

In T-SQL, I can create a table variable using syntax like

DECLARE @table AS TABLE (id INT, col VARCHAR(20))

For now, if I want to create an exact copy of a real table in the database, I do something like this

SELECT * 
FROM INFOMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MY_TABLE_NAME'

to check the column datatype and also max length, and start to create the @table variable, naming the variable, datatype and max_length one by one which is not very effective. May I know if there is any simpler way to do it like

DECLARE @table AS TABLE = SOME_REAL_TABLE_IN_DATABASE

Furthermore, is there any way to retrieve the column name, data type and max length of the column and use it directly in the declaration like

DECLARE @table AS TABLE (@col1_specs)

Thank you in advance.

EDIT: Thanks for the answers and comments, we can do that for @table_variable but only in dynamic SQL and it is not good for maintainability. However, we can do that using #temp_table.

Based on the answer by Ezlo, we can do something like this :

SELECT TABLE.* INTO #TEMP_TABLE FROM TABLE

For more information, please refer to this answer.

Difference between temp table and table variable (stackoverflow)

Difference between temp table and table variable (dba.stackexchange)

This feels like an XY question. Why do you want to use a variable and not a temporary table, for example? — Thom A, May 30 '19 at 08:49
I didn't think of create a temporary table when I ask this question, and I am also worry about the overhead of creating a table in the database is expensive. Thanks for the suggestion btw. — LEE Hau Chon, May 30 '19 at 08:58
Why do you believe that a variable will be less expensive? Just like a Temporary table, a table variable will be written to disc (in TempDB) if SQL Server can't fit it into memory, or feels that it would be optimal. Also, unless your using 2016+, SQL Server assumes that a table variable contains 1 row, which could cause poor execution plans choices if you you are insert more than one row into the variable ; which could have bigger impacts that the small amount of (extra) IO that a Temporary Table may need. — Thom A, May 30 '19 at 09:00

score 3 · Accepted Answer · edited May 30 '19 at 08:51

Object names and data types (tables, columns, etc.) can't be parameterized (can't come from variables). This means you can't do the following (which would be required to copy a table structure, for example):

DECLARE @TableName VARCHAR(50) = 'Employees'

SELECT
    T.*
FROM
    @TableName AS T

The only workaround is to use dynamic SQL:

DECLARE @TableName VARCHAR(50) = 'Employees'

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        T.*
    FROM
        ' + QUOTENAME(@TableName) + ' AS T '

EXEC (@DynamicSQL)

However, variables (scalar and table variables) declared outside the dynamic SQL won't be accessible inside as they lose scope:

DECLARE @VariableOutside INT = 10

DECLARE @DynamicSQL VARCHAR(MAX) = 'SELECT @VariableOutside AS ValueOfVariable'

EXEC (@DynamicSQL)

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@VariableOutside".

This means that you will have to declare your variable inside the dynamic SQL:

DECLARE @DynamicSQL VARCHAR(MAX) = 'DECLARE @VariableOutside INT = 10
                                    SELECT @VariableOutside AS ValueOfVariable'

EXEC (@DynamicSQL)

Result:

ValueOfVariable
10

Which brings me to my conclusion: if you want to dynamically create a copy of an existing table as a table variable, all the access of your table variable will have to be inside a dynamic SQL script, which is a huge pain and has some cons (harder to maintain and read, more prone to error, etc.).

A common approach is to work with temporary tables instead. Doing a SELECT * INTO to create them will inherit the table's data types. You can add an always false WHERE condition (like WHERE 1 = 0) if you don't want the actual rows to be inserted.

IF OBJECT_ID('tempdb..#Copy') IS NOT NULL
    DROP TABLE #Copy

SELECT
    T.*
INTO
    #Copy
FROM
    YourTable AS T
WHERE
    1 = 0

Thank you for the answer. If I uses the approach of `SELECT ... INTO ... [IN]`, and I create a table in the database. Later on, I delete the table after I `SELECT` what I needed from the temporary table, will there be any overhead comparing to `DECLARE` a `@table AS TABLE (...)` ? If yes, is this process expensive? — LEE Hau Chon, May 30 '19 at 08:51
Yes, temporary tables require a little bit more processing that table variables, but not as much as a physical table. The difference isn't much. All temporary tables will be immediately dropped by the server when the connection that created them is closed, so there is no need to drop them explicitly unless it's a heavy table (requires much space) and you will be keeping the connection alive for some reason. — EzLo, May 30 '19 at 08:59

score 1 · Answer 2 · answered May 30 '19 at 08:32

The answer for both questions is simple NO.

Although, I agree with you that T-SQL should change in this way.

In the first case, it means having a command to clone a table structure.

Of course, there is a possibility to make your own T-SQL extension by using SQLCLR.

Create table variable with exact same structure of another table

2 Answers2

Linked