What happens in EXEC, stays in EXEC. Lifespan of a MSSQL table variable

One of my all time favorite features of MSSQL 2005+ is being able to create table variables on the fly from SELECT statements. This isn’t a lesson in what table variables are, but here is an easy sample in case this is a new concept.

Running this query

SELECT * INTO #myTableVar FROM YourTable

Gives you a new table variable named myTableVar. Table variables are scoped to the active connection, so running this will work.

// make table var
SELECT * INTO #myTableVar FROM YourTable
// show me the data
SELECT * FROM #myTableVar
// you can drop it if you wish
DROP TABLE #myTableVar

However, let’s say you have an aspx page or a sproc that runs this query.

SELECT * INTO #myTableVar FROM YourTable

You can not access myTableVar in a separate connection to the database because as soon as the first query’s connection closes, myTableVar gets dropped.   Here are a few other scenarios that also demonstrate the scoping of a table variable.

-- FAILS
EXEC ('SELECT * INTO #tmp FROM MyTable;');
-- #tmp does not exist
SELECT * FROM #tmp

Table variable #tmp lives inside of EXEC

Here we see that the table variable #tmp only lives for the life of the statement inside of EXEC. The second SELECT * calls is outside of the EXEC statement.

-- #tmp2 works inside of EXEC statement
EXEC ('SELECT * INTO #tmp2 FROM MyTable; SELECT * FROM #tmp2');

What happens in EXEC, stays in EXEC

Here #tmp2 works because it’s being used inside of the EXEC statement. This is worth knowing if you work with dynamic sql statements and exec.

-- works!
SELECT * INTO #tmp FROM MyTable;
-- #tmp exists
SELECT * FROM #tmp

typical sample of using mssql table variable

This is a typical example that you may use inside a sproc, trigger, script, etc. Both sql calls live in the same space, so #tmp exists.