TSQL定义临时表(或表变量)而不定义模式?

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

是否有一种方法可以在不预先定义临时表的模式的情况下定义它?

3 个解决方案

#1

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.

实际上，使用一个表变量，一个内存中的表，是最理想的方法。#表在temp db中创建了一个表，并且##表是全局的—两个都有磁盘命中。考虑交易数量的下降/打击。

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.

这样做的缺点是，由于必须定义表变量，所以可能需要更长的时间来编写。

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

我还推荐RedGate为查询分析器提供SQL提示符。

#2

you don't need OPENQUERY. Just put "INTO #AnyTableName" between the select list and the FROM of any query...

你不需要OPENQUERY。在select列表和FROM查询之间输入"INTO #AnyTableName"…

SELECT *
    INTO #Temp1
    FROM table1
    WHERE x=y

#3

Yes, you can create it with

是的，你可以用它来创建

SELECT INTO ...

Let's say

假设

SELECT * INTO #t
FROM OPENQUERY( 'server',
'exec database.dbo.proc_name value1, value2, ... ' )

#1

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