問題描述

在 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

這篇關于創建與另一個表具有完全相同結構的表變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！