Temp Table Caching And Reuse

Shane O’Neill ran into an error with his stored procedure call:

We store the results in a temporary table first.

Don’t worry, that’s not the end of the post. That’s not even the point of this post. It is, however, what I was doing when I came across a weird error.

Let me show you!

INSERT EXEC failed because the stored procedure altered the schema of the target table.

He wasn’t really trying to alter the schema of that temp table, but read on to see the context and solution.

Related Posts

Checking For Temp Table Existence

Wayne Sheffield offers a clinic on temp tables: I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code: 2 IF exists (select * from tempdb..sysobjects where name like ‘#fg%’) DROP TABLE #fg Seeing this takes me back to one of my favorite presentations, where […]

Read More

Diagnosing TempDB Growth

Ginger Keys has a few tips around managing tempdb: So what happened to cause this inconsistent growth and how should I fix it? Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031