久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

創建與另一個表具有完全相同結構的表變量

Create table variable with exact same structure of another table(創建與另一個表具有完全相同結構的表變量)
本文介紹了創建與另一個表具有完全相同結構的表變量的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

在 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模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 成人欧美一区二区三区黑人孕妇 | 天天看天天爽 | 亚洲一区二区久久 | 中文字幕一级 | 草比av| www.黄色片视频| 黄色网址在线免费观看 | 亚洲国产成人精品久久 | 久久久久久黄 | 少妇性l交大片免费一 | 天天操天天射综合网 | 日韩成人在线观看 | 成人片在线看 | 精品久久一区 | 欧美精品一二区 | 欧美阿v | 国产婷婷 | 国产欧美在线 | 九九热这里 | 国产精品久久亚洲7777 | 成人毛片视频免费 | 日韩中文字幕在线 | 日本高清不卡视频 | 在线观看av免费 | 久久久久久久久久久一区二区 | 午夜欧美| 国产激情视频网站 | 成年人视频在线免费观看 | 亚洲精品乱码8久久久久久日本 | 亚洲欧美中文字幕在线观看 | 精品国产乱码久久久久久闺蜜 | av大片 | 成人福利在线视频 | 四虎永久在线精品免费一区二 | 天堂色网| 久久精品欧美一区二区三区不卡 | 国产精品久久久久久久久久软件 | 欧美精品一区二区三区蜜桃视频 | 日韩在线一区二区 | 免费一区| 国产激情 |