TSQL Define Temp Table (or table variable) Without Defining Schema?

Question

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

Solution 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.

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.

Solution 2

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

SELECT *
    INTO #Temp1
    FROM table1
    WHERE x=y

Solution 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, ... ' )

Comments

[Chris Burgess]

or Select * into #Temp1 from table1 where 0 = 1

[Jeff]

However, the table variable must be defined at compile rather than run time right? For this I needed a dynamically generated table.

[BlueRaja - Danny Pflughoeft]

Ugh, but this is so verbose, especially since TSQL doesn't have an "Insert-or-update" statement...

[James Manning]

@BlueRaja-DannyPflughoeft - you can use the MERGE command to get 'insert or update' behavior. msdn.microsoft.com/en-us/library/bb522522.aspx

[Trajanus]

The idea that table variables are faster because they are only in memory seems to be incorrect. According to this Microsoft FAQ table variables can be stored in tempdb since they could potentially be larger than memory can hold. Its Question/Answer #4.

[Gerard ONeill]

Not only not answering the question, but wrong on when and why to use table variables. Speedwise, it depends on how you are gonna query it, and how large it will be. There are no statistics. You cannot create secondary indexes. You cannot alter it. There is also no rollback. And the scope is very limited. Which leads to some of its advantages too.

[lanartri]

Table variable is not logged. That's why it is definitely faster for small amounts of data.

[Matthieu]

Don't forget to do a drop table #Temp1 at the end of your procedure if you use this method

[Jacob Stamm]

Please update answer to remove the erroneous information about table variables bring in-memory. Refer to this answer for accurate information on table variables vs temp tables.