Select Into Explanation / Temporary Tables

Question

Based on the tutorial on SQL Temporary Tables, it should be OK to create a temp table by using SELECT * INTO #tempTable FROM tableA but it's throwing me SQLException when I trying to SELECT * FROM #tempTable saying that Invalid object name '#tempTable'. May I know what's the proper way of using a temp table in C#?

string sql = "SELECT * INTO ##tempTable FROM (SELECT * FROM tableA)";
using (var command = new SqlCommand(sql, connection))
{
    string sqlNew = "SELECT * FROM ##tempTable";
    using (var command2 = new SqlCommand(sqlNew, connection))
    {
        using (var reader = command2.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["column1"].ToString());

            }
            Console.ReadLine();
        }
    }
}

My Objective is tryint to using the data retrieved from sqlVar and insert them into a tempTable and perform some operation on it. Very much appreciated if there is some sample code on how to fit the code into the above code. Thank You.

Temporary tables available during the session that creates them. — Hamlet Hakobyan, Sep 12 '13 at 07:48
@HhamletHakobyan: I'm sorry that I couldn't figure out how to fix my problem with your description. — SuicideSheep, Sep 12 '13 at 08:01
Why are you doing this in a temp table? what actions do you need to perform against the data? can you do this in the C# code? — Christian Phillips, Sep 12 '13 at 08:02
@christiandev: I need to store them into a tempTable so that in my other method I can access the post-processed table again. — SuicideSheep, Sep 12 '13 at 08:06
If you create a class with the fields you are going to work on, then you can use this for the processing and keep in memory until you're finished with it, and then store back to DB, or whatever you want to do with it. It's difficult to be more sepcific without knowing what you need to do with the data. — Christian Phillips, Sep 12 '13 at 08:10
@christiandev: I just want to know how temporary table works in C#. Please tell me how to create a tempTable in C# and just select data from them? — SuicideSheep, Sep 12 '13 at 08:22
Temp tables is a SQL specific concept. So, within a stored procedure for example, you can store a subset of data in a temp table, work with that in the query and then, simple example here : http://devzone.advantagedatabase.com/dz/webhelp/Advantage7.1/adssql/using_temporary_tables_in_sql_statements.htm — Christian Phillips, Sep 12 '13 at 08:49
You're not executing the SQL code that selects in to the temporary table, so it is never created! See my answer. — Thorsten Dittmar, Sep 13 '13 at 06:42

Dhaval · Accepted Answer · 2013-09-12T08:21:47.953

But why you need temp table at SQL server side..

1) if you wish to perform operation on C# side just take data in DATASET instead of DATAREADER .. and

 DataSet dataset = new DataSet();
 using (SqlConnection conn = new SqlConnection(connString))
 {
     SqlDataAdapter adapter = new SqlDataAdapter();                
     adapter.SelectCommand = new SqlCommand("select * from tableA", conn);
     conn.Open(); 
     adapter.Fill(dataset);
     conn.Close(); 
     foreach (DataRow row in dataset.Tables[0]) // Loop over the rows.
    {
        // perform your operation
    }
 }

2) if you need to perform operation on SQL side then create a stored procedure at SQL server .. in the stored procedure create #table and use it ..

3) and you do not want to create DATASET then you can take data LIST and perform your operation on C# side

score 4 · Answer 2 · answered Sep 13 '13 at 06:41

You are not executing the first command at all, so the SELECT INTO isn't executed, so the temporary table is not created, so you get an error about the table not existing.

The code should read:

string sql = "SELECT * INTO ##tempTable FROM (SELECT * FROM tableA)";
using (var command = new SqlCommand(sql, connection))
{
    command.ExecuteNonQuery(); // <-- THIS

    string sqlNew = "SELECT * FROM ##tempTable";
    using (var command2 = new SqlCommand(sqlNew, connection))
    {
        using (var reader = command2.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["column1"].ToString());

            }
            Console.ReadLine();
        }
    }
}

Yousri Mousa · Answer 3 · 2018-08-12T22:36:20.020

1-SELECT * INTO # tempTable FROM tableA (local temp)or 2-SELECT * INTO ## tempTable FROM tableA (global temp)then

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Both, Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

temp table in SQL DB

score 0 · Answer 4 · answered Sep 12 '13 at 07:57

0

Change your temp table from #tempTable to ##tempTable.

Using ## means a global temp table that stays around. You'll need to Drop it after you have completed your task.

If Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#tempTable'))

DROP TABLE #tempTable

answered Sep 12 '13 at 07:57

Nadeem_MK

7,533
7
50
61

It's still throwing me the same error. I've updated my question, please see if its the correct way that you've mentioned? – SuicideSheep Sep 12 '13 at 08:09
See my answer. You need to create a temporary table before you can use it! – Thorsten Dittmar Sep 12 '13 at 08:53

Yosi Dahari · Answer 5 · 2013-09-12T08:43:39.093

0

I think your answer is in the comment:

Temporary tables available during the session that creates them.

If you want to actualy get the data you have to perform a SELECT statement from this temporary table within the same scope.

One more thing:

I don't see you are executing the var command, you are missing this line:

string sql = "SELECT * INTO ##tempTable FROM (SELECT * FROM tableA)";
using (var command = new SqlCommand(sql, connection))
{
    command.ExecuteNonQuery();// This line is missing..
    string sqlNew = "SELECT * FROM ##tempTable";
    using (var command2 = new SqlCommand(sqlNew, connection))
    {
        using (var reader = command2.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["column1"].ToString());

            }
            Console.ReadLine();
        }
    }
}

But missing the line isn't the reason why your implementation is wrong..

edited Sep 12 '13 at 08:43

answered Sep 12 '13 at 08:27

Yosi Dahari

6,794
5
24
44

As I've said, I get an exception when I trying to select the temporary table with exception `Invalid object name '#tempTable'`. All operation are within the same scope – SuicideSheep Sep 12 '13 at 08:32
I thought I have `command2.ExecuteReader()`? Please show me how to do it, please – SuicideSheep Sep 12 '13 at 08:37
This is not the main part of my answer, you are doing something fundamentally wrong... – Yosi Dahari Sep 12 '13 at 08:54

Select Into Explanation / Temporary Tables

5 Answers5