MySql - Create Table If Not Exists Else Truncate?

Shmuel613, it would be better to update your original question rather than replying. It's best if there's a single place containing the complete question rather than having it spread out in a discussion Ben's answer is reasonable, except he seems to have a 'not' where he doesn't want one. Dropping the table only if it doesn't exist isn't quite right You will indeed need multiple statements.

Either conditionally create then populate: CREATE TEMPORARY TABLE IF NOT EXISTS fubar ( id int, name varchar(80) ) TRUNCATE TABLE fubar INSERT INTO fubar SELECT * FROM barfu or just drop and recreate DROP TABLE IF EXISTS fubar CREATE TEMPORARY TABLE EXISTS fubar SELECT id, name FROM barfu With pure SQL those are your two real classes of solutions. I like the second better (With a stored procedure you could reduce it to a single statement. Something like: TruncateAndPopulate(fubar) But by the time you write the code for TruncateAndPopulate() you'll spend more time than just using the SQL above.).

Shmuel613, it would be better to update your original question rather than replying. It's best if there's a single place containing the complete question rather than having it spread out in a discussion. Ben's answer is reasonable, except he seems to have a 'not' where he doesn't want one.

Dropping the table only if it doesn't exist isn't quite right. You will indeed need multiple statements. Either conditionally create then populate: CREATE TEMPORARY TABLE IF NOT EXISTS fubar ( id int, name varchar(80) ) TRUNCATE TABLE fubar INSERT INTO fubar SELECT * FROM barfu or just drop and recreate DROP TABLE IF EXISTS fubar CREATE TEMPORARY TABLE EXISTS fubar SELECT id, name FROM barfu With pure SQL those are your two real classes of solutions.

I like the second better. (With a stored procedure you could reduce it to a single statement. Something like: TruncateAndPopulate(fubar) But by the time you write the code for TruncateAndPopulate() you'll spend more time than just using the SQL above.).

Execute any query if table exists. Usage: call Edit_table(database-name,table-name,query-string); Procedure will check for existence of table-name under database-name and will execute query-string if it exists. Following is the stored procedure: DELIMITER $$ DROP PROCEDURE IF EXISTS `Edit_table` $$ CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20),in_tbl_nm varchar(20),in_your_query varchar(200)) DETERMINISTIC BEGIN DECLARE var_table_count INT; select count(*) INTO @var_table_count from information_schema.

TABLES where TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm; IF (@var_table_count > 0) THEN SET @in_your_query = in_your_query; #SELECT @in_your_query; PREPARE my_query FROM @in_your_query; EXECUTE my_query; ELSE select "Table Not Found"; END IF; END $$ DELIMITER ; More on Mysql.

You could do the truncate after the 'create if not exists'. That way it will always exist... and always be empty at that point. CREATE TABLE fubar IF NOT EXISTS TRUNCATE TABLE fubar.

OK then, not bad. To be more specific, the current query is doing something like: $sql1 = "TRUNCATE TABLE fubar"; $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS fubar SELECT id, name FROM barfu"; The first time the method containing this is run, it generates an error message on the truncate since the table doesn't exist yet. Is my only option to do the "CREATE TABLE", run the "TRUNCATE TABLE", and then fill the table?(3 separate queries) PS - thanks for responding so quickly!

If you're using PHP, use mysql_list_tables to check that the table exists before TRUNCATE it.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.