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?

Remember

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

Barrow

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

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

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

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. – Marcellusmarcelo

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. – Rositaroskes

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

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. – Wera

Answer 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

Ductile

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

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

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

Klopstock