Question
In my program, I want to select some bookIDs into a tempDB for later queries like this (using Dapper extension):
using (var conn = new SqlConnection(connStr)) {
conn.Execute("SELECT bookID INTO #tempdb WHERE ... FROM Books");
int count = conn.ExecuteScalar<int>("SELECT COUNT(*) FROM #tempdb");
var authors = conn.Query("SELECT * FROM #tempdb LEFT JOIN BookAuthors ON ...");
}
However when I execute the page, I get following exception:
Invalid object name '#tempdb'.
It seems that life-cycle of #tempdb is only valid in first query ?
Expert Answer
It looks like you're using the implicit connection opening/closing. This will indeed cause problems with transient objects. If you need temp tables between queries, you will need to manually open the connection before you execute any such queries. This should then work fine, and many examples in the test suite make use of temp tables in the way.
However, from a practical standpoint, making use of temporary tables to transfer state between queries is ... awkward. In addition to being brittle, it isn't good for the plan cache, as #foo has a different meaning between all uses on different connection (including reset but reused connections).
Popular Answer
I found a previous poster who met the same problem and his solution.
The post indicates that you have to "CREATE TABLE #tempdb" explicitly in your SQL first and everything goes fine. Even the poster himself don't know why such style of coding works.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow