时间戳

首页 > 解决方案 > 创建具有与另一个表完全相同的结构的表变量

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

问题描述

在 T-SQL 中，我可以使用如下语法创建表变量

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

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

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

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

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)

标签： sql-servertsql

解决方案

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

DECLARE @TableName VARCHAR(50) = 'Employees'

SELECT
    T.*
FROM
    @TableName AS T

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

DECLARE @TableName VARCHAR(50) = 'Employees'

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

EXEC (@DynamicSQL)

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

DECLARE @VariableOutside INT = 10

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

EXEC (@DynamicSQL)

Msg 137, Level 15, State 2, Line 1
必须声明标量变量“@VariableOutside”。

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

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

EXEC (@DynamicSQL)

结果：

ValueOfVariable
10

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

一种常见的方法是使用临时表。创建SELECT * INTO它们将继承表的数据类型。如果您不希望插入实际行，则可以添加始终为假的WHERE条件（如）。WHERE 1 = 0

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

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

推荐阅读