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