473,561 Members | 3,656 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Drop temporary tables whilst connected ?

Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.

Example

SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.

TIA

Ryan
Jul 20 '05 #1
2 4016
Ryan (ry********@hot mail.com) writes:
Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.


No, there is no "DROP TABLE #%".

You could write a cursor over tempdb..sysobje cts which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.

On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.

If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
> On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.


Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.

In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.

Thanks

Ryan
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5465
by: Rod Davis | last post by:
Once a week, we run a script to drop all the indexes on tables in our db, followed by another to add those indexes back (we insert every day, so we do this to prevent index fragmenttation). The database is named BX, and the script runs these two commands: c:\mysql\bin\mysql BX < dropIndex.sql c:\mysql\bin\mysql BX < addIndex.sql We're...
5
2846
by: Jay | last post by:
Hey there, I have a question. Is it possible to to construct something through a combination of php and javascript that (when a browser window is closed) would drop temp tables that are used? I've tried to use an override of the SESSION functions, but that isn't working in the way I would like it to. I go into mysql and can see that the temp...
0
2597
by: Bart | last post by:
Hello Sometimes i need to drop one of my tables. DROP TABLE removes all foreign keys connected with that table. After this i need to recreate FK. It is not easy to find dropped keys, so i have script to create all keys in database (ALTER TABLE ... ). It works good, but when FK exists, it will create another (exacly the same).
1
4145
by: ezra epstein | last post by:
I found a post about something similar in an older release: http://archives.postgresql.org/pgsql-bugs/2002-08/msg00151.php Here's the issue. Inside a function I'm calling CREATE LOCAL TEMPORARY TABLE ds_copy_item_meta ( LIKE merchandise.item_meta EXCLUDING DEFAULTS ) on COMMIT DROP; Of course it might be that the ON COMMIT DROP is...
2
6420
by: robert | last post by:
the real DBAs aren't around for a few days, so rather than shoot myself in a tender spot, i thought i'd ask first. what docs i can find say that the syntax is: DROP TABLESPACE FOO seems simple enough, except that the other side is CREATE TABLESPACE FOO IN BARx
14
9090
by: deko | last post by:
Can the DROP TABLE statement be used with a select or where statement? DROP TABLE SELECT * FROM tblTablesImported WHERE Import_ID Not In (SELECT FROM tblTablesInternal); Or do I have to supply a parameter like: DROP TABLE Sheet1
8
2000
by: Martijn van Oosterhout | last post by:
Currently you can create temporary tables that are deleted at the end of the session. But how about temporary views? It's just a table with a rule so I don't imagine it would be terribly difficult. Are there any issues I havn't thought of? While we're at it, what about temporary functions? -- Martijn van Oosterhout <kleptog@svana.org> ...
6
7700
by: Peter Nurse | last post by:
For reasons that are not relevant (though I explain them below *), I want, for all my users whatever privelige level, an SP which creates and inserts into a temporary table and then another SP which reads and drops the same temporary table. My users are not able to create dbo tables (eg dbo.tblTest), but are permitted to create tables under...
2
2170
by: stefan.albert | last post by:
Hello Ravi, what you want to do is very tricky, but possible. I've had this same problem - we want to identify the top user of tempspace and eventually force him off when using too much space (FS runs full). For temptables (NON-DGTT) you can use db2pd to find the owner (- tcbstats) here the application handle (agent id) is listed. But this...
0
7647
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7618
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7930
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5472
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3617
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3600
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2068
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1181
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
896
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.