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?

score 42 · Accepted Answer · edited Feb 21 '12 at 20:09

42

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.

edited Feb 21 '12 at 20:09

casperOne

73,706
19
184
253

answered Jan 29 '10 at 00:08

ElHaix

12,846
27
115
203

1

However, the table variable must be defined at compile rather than run time right? For this I needed a dynamically generated table. – Jeff Jan 31 '10 at 18:53
2

Ugh, but this is so verbose, especially since TSQL doesn't have an "Insert-or-update" statement... – BlueRaja - Danny Pflughoeft Feb 21 '12 at 20:15
@BlueRaja-DannyPflughoeft - you can use the MERGE command to get 'insert or update' behavior. http://msdn.microsoft.com/en-us/library/bb522522.aspx – James Manning Sep 07 '12 at 19:17
8

The idea that table variables are faster because they are only in memory seems to be incorrect. According to [this](http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k) Microsoft FAQ table variables can be stored in tempdb since they could potentially be larger than memory can hold. Its Question/Answer #4. – Trajanus Oct 31 '12 at 15:54
5

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. – Gerard ONeill Aug 26 '13 at 21:44
Table variable is *not logged*. That's why it is definitely faster for small amounts of data. – iDevlop Jan 24 '14 at 09:28
Please update answer to remove the erroneous information about table variables bring in-memory. Refer to [this answer](https://stackoverflow.com/a/13777841/4028303) for accurate information on table variables vs temp tables. – Jacob Stamm Jan 22 '20 at 19:29

score 30 · Answer 2 · answered Mar 27 '09 at 21:17

30

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

answered Mar 27 '09 at 21:17

KM.

101,727
34
178
212

or Select * into #Temp1 from table1 where 0 = 1 – Chris Burgess Mar 31 '09 at 18:14
5

Don't forget to do a `drop table #Temp1` at the end of your procedure if you use this method – Matthieu Jul 22 '15 at 09:17

score 11 · Answer 3 · answered Mar 27 '09 at 01:59

11

Yes, you can create it with

SELECT INTO ...

Let's say

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

answered Mar 27 '09 at 01:59

boj

10,935
5
38
57

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

3 Answers3

Linked