TSQL定义临时表(或表变量)而不定义模式? [英] TSQL Define Temp Table (or table variable) Without Defining Schema?

问题描述

有没有一种方法可以定义一个临时表而不预先定义它的模式?

Is there a way to define a temp table without defining it's schema up front?

推荐答案

实际上，使用表VARIABLE(内存表)是最佳的选择. #table在temp db中创建一个表，而## table是全局的-两者都有磁盘命中.考虑交易数量的减慢/打击.

Actually using a table VARIABLE, an in-memory table, is the optimal way to go. The #table creates a table in temp db, and ##table is global - both with disk hits. Consider the slow-down/hit experienced with the number of transactions.

CREATE PROCEDURE [dbo].[GetAccounts] 
    @AccountID BIGINT,
    @Result INT OUT,
    @ErrorMessage VARCHAR(255) OUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @Result = 0
    SET @ErrorMessage = ''

    DECLARE @tmp_Accounts TABLE (
                                                AccountId BIGINT,
AccountName VARCHAR(50),
...
)

INSERT INTO @tmp_Accounts ([AccountId], [AccountName]...
)
SELECT AccountID, AccountName
FROM Accounts
WHERE  ...


    IF @@Rowcount = 0
        BEGIN
            SET @ErrorMessage = 'No accounts found.'
            SET @Result = 0

            RETURN @Result
        END
    ELSE
        BEGIN
            SET @Result = 1

            SELECT *
            FROM @tmp_Accounts
        END 

请注意您插入此临时表的方式.

Note the way you insert into this temp table.

缺点是编写代码可能要花费一些时间，因为您必须定义表变量.

The down-side of this is that it may take a bit longer to write, as you have to define your table variable.

我还建议RedGate使用SQL Prompt for Query Analyzer.

I'd also recommend SQL Prompt for Query Analyzer by RedGate.

这篇关于TSQL定义临时表(或表变量)而不定义模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！