Solved

temporary table already exists on second run after query fails on first run

Posted on 2011-09-19
4
348 Views
Last Modified: 2012-05-12
I have a script where I create a temporary table called 'tempticks'.
Occasionally the query fails, and then I get an error when trying to run it later...

table 'tempticks' already exists.

My Question:- Can I do a check for a table prior to creation, or can I 'autodelete' a table (in case it exists) at the start of a mysql query? I really just want my query to be as rock solid as possible.

See my other open questions for the full query, or further details.
0
Comment
Question by:XenekStoehr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Accepted Solution

by:
mansooralia_yahoo earned 500 total points
ID: 36559253
check this link: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html it will delete the table if it exists.
0
 
LVL 4

Expert Comment

by:AnuTiji
ID: 36559258
Hi

Please find the below TSQL to check if the temporary table exists

IF object_id('tempdb..#tempticks) IS NOT NULL
BEGIN
   DROP TABLE #tempticks
END
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 36559272
0
 
LVL 1

Author Closing Comment

by:XenekStoehr
ID: 36596692
Ok, well, that was easy. I must have missed the part of the manual that referred to the "if exists" section.
This is the line I added to achieve a perfectly working solution. I added this line prior to the create table line...

DROP TABLE IF EXISTS tempticks;

Thank you Mansooralia
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question