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