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.

Answer 1

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