I&#39;m learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table.

But sometimes, if there are too many records in a table variable that can not be contained in memory, the table variable will be put on disk like the temp table.

But I don&#39;t know what the &quot;too many records&quot; is. 100,000 records? or 1000,000 records? How can I know if a table variable I&#39;m using is in memory or is on disk? Is there any function or tool in SQL Server 2005 to measure the scale of the table variable or letting me know when the table variable is put on disk from memory?

When should I use a table variable vs temporary table in sql server?

I am trying to create a temporary table that selects only the data for a certain `register_type`. I wrote this query but it does not work:

    $ CREATE TABLE temp1
    (Select 
    	egauge.dataid,
    	egauge.register_type,
    	egauge.timestamp_localtime,
    	egauge.read_value_avg
    from rawdata.egauge
    where register_type like &#39;%gen%&#39;
    order by dataid, timestamp_localtime ) $

I am using PostgreSQL.  
Could you please tell me what is wrong with the query?

Creating temporary tables in SQL

I have a problem to decide whether to use a view or a temp table.

I have a stored procedure that i call from program. In that SP i store the result of a long query in a temp table, name the columns and make another queries on that table store the results in labels or a gridview or whatever and drop the Temp Table. I could also store the query-result in a view and make queries on that view. So what is better or in what case do i HAVE to use a VIEW/ Temp Table.

According to my research a view has the benefit of: Security, Simplicity and Column Name Specification. My temporary table fulfills all that too (according to my opinion).  

View or Temporary Table - which to use in MS SQL Server?

After having created a temporary table and declaring the data types like so;

    CREATE TABLE #TempTable(
    ID int,
    Date datetime,
    Name char(20))

How do I then insert the relevant data which is already held on a physical table within the database?

Inserting data into a temporary table

My database contains three tables called `Object_Table`, `Data_Table` and `Link_Table`. The link table just contains two columns, the identity of an object record and an identity of a data record.

I want to copy the data from `DATA_TABLE` where it is linked to one given object identity and insert corresponding records into `Data_Table` and `Link_Table` for a different given object identity.

I **can** do this by selecting into a table variable and the looping through doing two inserts for each iteration.

Is this the best way to do it?

**Edit** : I want to avoid a loop for two reason, the first is that I&#39;m lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.

I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?

SQL Server: Is it possible to insert into two tables at the same time?

What is the difference between local and global temporary tables in SQL Server?

Local and global temporary tables in SQL Server

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don&#39;t change the columns. If I add a column later, it will give an error saying &quot;invalid column&quot;. Please let me know what I am doing wrong.

    IF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL
        DROP TABLE #Results

    CREATE TABLE #Results
    (
        Company                CHAR(3),
        StepId                TINYINT,
        FieldId                TINYINT,
    )

    select company, stepid, fieldid from #Results

    --Works fine to this point

    IF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL
        DROP TABLE #Results

    CREATE TABLE #Results
    (
        Company                CHAR(3),
        StepId                TINYINT,
        FieldId                TINYINT,
        NewColumn            NVARCHAR(50)
    )

    select company, stepid, fieldid, NewColumn from #Results

    --Does not work


Check if a temporary table exists and delete if it exists before creating a temporary table

Which are more performant, `CTE` or `Temporary Tables`?

Which are more performant, CTE or temporary tables?

I would like to create a temporary table in a Oracle database

something like 

    Declare table @table (int id)

In SQL server

And then populate it with a select statement

Is it possible?

Thanks

How do you create a temporary table in an Oracle database?

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I want to re-use.

It would save time if I did not have to write up a create table command and keep the column list and type list matched up.

Create a temporary table in a SELECT statement without a separate CREATE TABLE

I&#39;m currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every &quot;tag&quot; to filter on.

The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN

This basically looks like:

    SELECT
        *
    FROM search
    INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
    INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
    ...
    INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN

This works but I would much prefer the &quot;search&quot; table to be temporary (it can be several orders of magnitude smaller if it isn&#39;t a normal table) but that gives me a very annoying error: &lt;code&gt;Can&#39;t reopen table&lt;/code&gt;

Some research leads me to [this bug report][1] but the folks over at MySQL don&#39;t seem to care that such a basic feature (using a table more than once) does not work with temporary tables. I&#39;m running into a lot of scalability problems with this issue.

Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?

Kind regards, Kris

[additional]

The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs. However, It did help me solve an earlier problem so now the table, temp or not, is no longer required. We were just thinking too generic for our problem. The entire application of filters has now been brought back from around a minute to well under a quarter of a second.


  [1]: http://bugs.mysql.com/bug.php?id=10327

Getting around MySQL &quot;Can&#39;t reopen table&quot; error

It [appears](https://stackoverflow.com/q/1433978/1593077) that MySQL doesn&#39;t have array variables. What should I use instead? 

------
There seem to be two alternatives suggested: A **set-type scalar** and **temporary tables**. The question I linked to suggests the former. But is it good practice to use these instead of array variables? Alternatively, if I go with sets, what would be the set-based idiom equivalent to `foreach`? 

How can I simulate an array variable in MySQL?

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use `ALTER TABLE` because this causes an implicit commit.

Therefore I&#39;m looking for the syntax to add the `INDEX` for `tempid` during creation. Can anyone be of help?

    CREATE TEMPORARY TABLE tmpLivecheck 
    (
        tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
    )
    SELECT *
    FROM   tblLivecheck_copy
    WHERE  tblLivecheck_copy.devId = did;


Create a temporary table in MySQL with an index from a select

I need to perform a query 2.5 million times. This query generates some rows which I need to `AVG(column)` and then use this `AVG` to filter the table from all values below average. I then need to `INSERT` these filtered results into a table. 

The only way to do such a thing with reasonable efficiency, seems to be by creating a `TEMPORARY TABLE` for each query-postmaster python-thread. I am just hoping these `TEMPORARY TABLE`s will not be persisted to hard drive (at all) and will remain in memory (RAM), unless they are out of working memory, of course.

I would like to know if a TEMPORARY TABLE will incur disk writes (which would interfere with the INSERTS, i.e. slow to whole process down)

PostgreSQL temporary tables

In SQL Server 2005, we can create temp tables one of two ways:

    declare @tmp table (Col1 int, Col2 int);

or

    create table #tmp (Col1 int, Col2 int);

What are the differences between these two?  I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.  

In which scenarios does one out-perform the other? 



What&#39;s the difference between a temp table and table variable in SQL Server?

Is there any SQL subquery syntax that lets you define, literally, a temporary table?

For example, something like

    SELECT
      MAX(count) AS max,
      COUNT(*) AS count
    FROM
      (
        (1 AS id, 7 AS count),
        (2, 6),
        (3, 13),
        (4, 12),
        (5, 9)
      ) AS mytable
      INNER JOIN someothertable ON someothertable.id=mytable.id

This would save having to do two or three queries: creating temporary table, putting data in it, then using it in a join.

I am using MySQL but would be interested in other databases that could do something like that.

Can you define &quot;literal&quot; tables in SQL?

I&#39;m using SQL Server 2008. Say I create a temporary table like this one:

    create table #MyTempTable (col1 int,col2 varchar(10))

How can I retrieve the list of fields dynamically? I would like to see something like this:

    Fields:
    col1
    col2

I was thinking of querying sys.columns but it doesn&#39;t seem to store any info about temporary tables. Any ideas?

How to retrieve field names from temporary table (SQL Server 2008)

I realize that temporary tables are session/connection bound and not visible or accessible out of the session/connection.

I have a long running stored procedure that creates temporary tables at various stages.

Is there a way I can see the list of current temporary tables?
What privileges do I need to be able to do so?

Alternatively,

Is there a way I can see the particular SQL statement being executed inside a running stored procedure? The procedure is running as a scheduled job in SQL Server.

I am using SQL Server 2000.

Thanks for your guidance. 

Is there a way to get a list of all current temporary tables in SQL Server?

I have the following issue in SQL Server, I have some code that looks like this:

    DROP TABLE #TMPGUARDIAN
    CREATE TABLE #TMPGUARDIAN(
    LAST_NAME NVARCHAR(30),
    FRST_NAME NVARCHAR(30))  
                     
    SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN  FROM TBL_PEOPLE

When I do this I get an error &#39;There is already an object named &#39;#TMPGUARDIAN&#39; in the database&#39;. Can anyone tell me why I am getting this error?

Temporary table in SQL server causing &#39; There is already an object named&#39; error

I have query in a stored procedure that calls some linked servers with some dynamic SQL. I understand that EF doesn&#39;t like that, so I specifically listed all the columns that would be returned. Yet, it still doesn&#39;t like that. What am I doing wrong here? I just want EF to be able to detect the columns returned from the stored procedure so I can create the classes I need.

Please see the following code that makes up the last lines of my stored procedure:

    SELECT
	    #TempMain.ID,
	    #TempMain.Class_Data,
	    #TempMain.Web_Store_Class1,
	    #TempMain.Web_Store_Class2,
	    #TempMain.Web_Store_Status,
	    #TempMain.Cur_1pc_Cat51_Price,
	    #TempMain.Cur_1pc_Cat52_Price,
    	#TempMain.Cur_1pc_Cat61_Price,
	    #TempMain.Cur_1pc_Cat62_Price,
	    #TempMain.Cur_1pc_Cat63_Price,
	    #TempMain.Flat_Length,
	    #TempMain.Flat_Width,
	    #TempMain.Item_Height,
	    #TempMain.Item_Weight,
	    #TempMain.Um,
	    #TempMain.Lead_Time_Code,
	    #TempMain.Wp_Image_Nme,
	    #TempMain.Wp_Mod_Dte,
	    #TempMain.Catalog_Price_Chg_Dt,
	    #TempMain.Description,
	    #TempMain.Supersede_Ctl,
	    #TempMain.Supersede_Pn,
    	TempDesc.Cust_Desc,
	    TempMfgr.Mfgr_Item_Nbr,
	    TempMfgr.Mfgr_Name,
	    TempMfgr.Vendor_ID
    FROM
    	#TempMain
    		LEFT JOIN TempDesc ON #TempMain.ID = TempDesc.ID
		    LEFT JOIN TempMfgr ON #TempMain.ID = TempMfgr.ID

All Temp Tables Solutions on Gang of Coders

Total of 20 Temp Tables Solutions