How to create a temporary table that's local to the connection, without being forced to use a simple explicit create table statement?

Question

I want to create a temporary table that's local to the SqlConnection, using a statement of the form "select into #tempTable", because I do not have to specify column names and types, as I would with a "create table #tempTable" statement;

I want the temp table to be visible from subsequent SqlCommands running on the same already-open SqlConnection, and only on that connection. I don't want to use global temporary tables, because I don't want to have to deal with name conflicts when the query is run concurrently.

I discovered a local temp table is visible to subsequent SqlCommands only if it's created with a simple, lone "create table #tempTable" statement with no SQL parameters. On the other hand, if it's created with a "select into" statement with parameters (or strung together with other statements in a single SqlCommand), then that causes the whole thing to be executed in a procedure, which limits the scope of the temp table to that procedure.

In general, it seems impossible to use temp tables across SqlCommands when those SqlCommands are anything other than simple "create table" statements with no parameters, because of how the SqlCommand works internally.

Creating the temp table separately would work (i.e. a simple "create table #tempTable (col type, col type, etc.)" statement with no SQL parameters), but that involves explicitly specifying all the column types (which is too much maintenance, IMO), whereas with a "select into" statement, I don't have to specify the column types because they're matched to the source columns automatically.

I want a temporary table created with "select into" to persist as long as the connection is open. Is there a way to scope the table to the open connection?

As it stands now, it's really unpredictable behavior, because depending on how the SqlCommand is constructed, it may or may not wrap itself in a stored procedure, so a temporary table created within a SqlCommand may or may not be visible to subsequent SqlCommands run on that same open connection.

Not a 100% sure I understand what you mean by "executed in a procedure" but if you need multiple connections to access a temporary table, why not use a global temp table? SELECT INTO ##globalTemp — acg
Because a global temp table would cause name conflicts with every other instance of the command running at the same time. — Triynko
This is within a stored proc or dynamic sql? I get the feeling its dynamic, which means you can tokenize the sql and share a unique table name. — acg
It's with an SqlCommand object in .NET. It's sort of a black box, in the sense that I don't know whether it's issuing statements directly, or running them as dynamic SQL. I get the feeling that the answer is "it depends on the sql query text", and that's the problem. I need a way to ensure a table is scoped to the open connection, rather than a particular procedure (+child procedures). — Triynko
It's doesn't use multiple connections, it uses multiple SqlCommand objects on the same, already-open SqlConnection. The problem is that "new SqlCommand( "create table #tempTable" ), will be visible in subsequent SqlCommands, but one like "new SqlCommand("select * into #tempTable from X; select * from #tempTable;")" will result in the statement being executed in a procedure (as dynamic SQL?) such that it's dropped at the end and not visible to subsequenct SqlCommands on that same connection. It entirely depends on what the text looks like that's passed to SqlCommand when the table is created. — Triynko

Sam Axe Sam Axe · Accepted Answer · 2015-02-25T22:31:09

As was pointed out in the comments, you are coding your SQL in the C# as C# strings. Thus you can create a temp table with any name you want.

I'll leave the implementation details up to you, but this fragment should get you on your way:

public function DoSomeSqlStuff() {
    var tempTableName = CreateTempTableName();
    using SqlConnection connection = new SqlConnection(connectionString) {
        using SqlCommand firstCommand = new SqlCommand("CREATE TABLE #" + tempTablename + "column definitions here", connection) {
            firstCommand.ExecuteNonQuery();
        }

        using SqlCommand secondCommand = new SqlCommand("SELECT * FROM #" + tempTableName, connection) {
            secondCommand.ExecuteReader();
        }

        using SqlCommand thirdCommand = new SqlCommand("DROP TABLE #" + tempTableName, connection) {
            thirdCommand.ExecuteNonQuery();
        }
    }
}

certainly a simplistic example - but should get you set on the right path.

As for connection-scoped temp tables as a feature provided by the database... I've never heard of such a thing. Doesn't mean they don't exist.

How to create a temporary table that's local to the connection, without being forced to use a simple explicit create table statement?

1 Answers