I have declared a procedure which creates a temporary table like:

create table #parents(
    id integer null,
    )

now there is something like an insert into #parents using the result of a different procedure, let's say GetSpecialParents.

The GetSpecialParents procedure uses again a statement like

create table #parents(
    id integer null,
    )

Will the two temporary table definitions conflict with each other? Will both procedures use the same table, or will the tables be local to the procedure which created it?

So do I have to make sure, that all temporary tables which might be called in a sequence of procedures have a unique name?

asked 02 Feb '10, 17:15

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

1

Martin, for a sound explanation of differences between the different kinds of temporary tables, I would highly recommend Breck's book. Though it's focussed on V9, I've found these details really helpful.

(03 Feb '10, 11:03) Volker Barth

You (probably) don't have to worry about unique names.

There are four ways to create a local temporary table, and all of them have slightly different rules: CREATE TABLE #t, CREATE LOCAL TEMPORARY TABLE t, DECLARE LOCAL TEMPORARY TABLE t and SELECT INTO #t.

For CREATE TABLE #t, the table has nested scope and persistence.

If the CREATE TABLE #t is executed outside of any BEGIN block, the table name is visible to throughout all code running on that connection (unless overridden; see next paragraph), and the data persists until the table is explicitly dropped or the connection ends.

If the statement is executed within a BEGIN block, the table name is visible from that point on within the block, and the data persists, until the END is reached... at which point the table is dropped. If the table name is the same as one created outside the BEGIN, the outer table is not visible after the inner CREATE is executed... but the outer table becomes visible again after the END is reached.

Note that this form of CREATE doesn't have a COMMIT as a side effect. Also note the ON COMMIT clause can't be coded, but the commit action is the same as ON COMMIT PRESERVE ROWS.

In other words, CREATE TABLE #t works the way it should :)

permanent link

answered 02 Feb '10, 19:14

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101

question asked: 02 Feb '10, 17:15

question was seen: 762 times

last updated: 02 Feb '10, 19:14