创建与另一个表具有完全相同结构的表变量

在 T-SQL 中，我可以使用类似

的语法创建一个表变量

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

现在，如果我想在数据库中创建一个真实表的精确副本，我会做这样的事情

SELECT *来自 INFOMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'MY_TABLE_NAME'

检查列数据类型和最大长度，并开始创建@table变量，将变量、数据类型和max_length一一命名，这不是很有效.我可以知道是否有任何更简单的方法来做到这一点

DECLARE @table AS TABLE = SOME_REAL_TABLE_IN_DATABASE

另外，有没有什么办法可以检索到列的列名、数据类型和最大长度，并直接在声明中使用，比如

DECLARE @table AS TABLE (@col1_specs)

提前致谢.

感谢您的回答和评论，我们可以为 @table_variable 做到这一点，但只能在动态 SQL 中进行，并且不利于可维护性.但是，我们可以使用 #temp_table 来做到这一点.

根据 Ezlo 的回答，我们可以这样做:

SELECT TABLE.* INTO #TEMP_TABLE FROM TABLE

有关详细信息，请参阅此答案.

临时表和表变量的区别(stackoverflow)

临时表和表变量之间的差异 (dba.stackexchange)

解决方案

对象名称和数据类型(表、列等)不能参数化(不能来自变量).这意味着您不能执行以下操作(例如，复制表结构需要这样做):

DECLARE @TableName VARCHAR(50) = 'Employees'选择T.*从@TableName AS T

唯一的解决方法是使用动态 SQL:

DECLARE @TableName VARCHAR(50) = 'Employees'声明 @DynamicSQL VARCHAR(MAX) = '选择T.*从' + QUOTENAME(@TableName) + ' AS T '执行(@DynamicSQL)

但是，在动态 SQL 外部声明的变量(标量和表变量)将无法在内部访问，因为它们失去了作用域:

声明@VariableOutside INT = 10DECLARE @DynamicSQL VARCHAR(MAX) = 'SELECT @VariableOutside AS ValueOfVariable'执行(@DynamicSQL)

<块引用>

消息 137，级别 15，状态 2，第 1 行
必须声明标量变量@VariableOutside".

这意味着您必须在动态 SQL 中声明您的变量:

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

结果:

ValueOfVariable10

这使我得出我的结论:如果你想动态地创建一个现有表的副本作为表变量，你的表变量的所有访问都必须在一个动态 SQL 脚本中，这是一个巨大的痛苦和有一些缺点(更难维护和阅读，更容易出错等).

一种常见的方法是使用临时表.执行 SELECT * INTO 来创建它们将继承表的数据类型.如果您不想插入实际的行，您可以添加一个始终为假的 WHERE 条件(如 WHERE 1 = 0).

IF OBJECT_ID('tempdb..#Copy') 不是 NULL删除表#Copy选择T.*进入#复制从YourTable AS T在哪里1 = 0

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)

解决方案

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