Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gwoman
Starting Member
2 Posts |
 Posted - 2007-08-31 : 09:38:54
|
| Hi. I am trying to reuse the same temp table name in a script and even though I am dropping the table after each use, when I run the script it is telling me that the table object already exists... WHY??Example Script:select donor_id into #dfrom donorselect count(*) from #ddrop table #dselect donor_id, fullname into #dfrom donorselect count(*) from #ddrop table #d |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-31 : 09:50:11
|
| You need GOselect donor_id into #dfrom donorselect count(*) from #ddrop table #dGOselect donor_id, fullname into #dfrom donorselect count(*) from #ddrop table #dMadhivananFailing to plan is Planning to fail |
 |
|
|
gwoman
Starting Member
2 Posts |
Posted - 2007-08-31 : 10:24:09
|
| What if this is in a stored procedure where you cannot use another GO?? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-31 : 10:33:04
|
| If the structure is same then you can delete it and then insertOtherwise make use of more than one temp tablesMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-01 : 05:00:18
|
We use #SprocName_01, #SprocName_02 ... for temp table names within an Sproc. Not really in order to have separate tables, but more to avoid any possibility of code being moved around the Sproc and depending on a temp table which, by then, is "out of scope" in the original design (at least).I just think thus approach reduces future maintenance cost.If you are dropping the table immediately after use anyway I don't think using an additional name makes any difference to resource usage, but it does make a difference to "documenting" what your intentions are  Kristen |
|
|
|
|
|
|
reuse of temp table in same script