1

I'm using MySQL and trying to create a temp table. I will be doing a 2 while loop statements in PHP to populate the temp table. Firstly though I can't seem to get the Insert into temp table to work. I've tried many different versions of this, some using '#' for the table and various things (are there differences in SQL server and MySQL commands?). Here's my last attempt (P.S the Select statement works fine on its own).

CREATE TEMPORARY TABLE temp
(
aID varchar(15) NOT NULL,
bID varchar(15) NOT NULL
)
INSERT INTO temp
SELECT aID, bID
FROM tags
WHERE placeID = "abc" AND tagID = "def";

Help appreciated!

Also, just a general Q...this query will have to be run many times. Will using temp tables be OK or cause the server issues?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Stuart Durning
  • 47
  • 1
  • 1
  • 8

2 Answers2

0

You can create temporary table and insert select statemet in following way:

create temporary table temp
SELECT aID, bID
FROM tags
WHERE placeID = "abc" AND tagID = "def";

To drop the temporary table before creating it again. put following statement before creating temporary table:

drop temporary table if exists temp;

Note: It will be good if you can put all this code in stored procedure. and call it to create temporary table.

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
0

working on what Code-Monk wrote, consider the following:

drop procedure if exists uspK;
DELIMITER $$
create procedure uspK ()
BEGIN
    drop temporary table if exists temp; -- could be some other random structure residue

    create temporary table temp
    SELECT aID, bID
    FROM tags
    WHERE placeID = "abc" AND tagID = "def";

    -- use the temp table somehow
    -- ...
    -- ...
    -- ...

    drop temporary table temp; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

Test Stored Procedure

call uspK(); -- test it, no warnings on edge conditions

What not to do

One would not find much luck with the following. If you think so, run it a few times;

drop procedure if exists uspK;
DELIMITER $$
create procedure uspK ()
BEGIN
    -- drop temporary table if exists temp;

    create temporary table if not exists temp
    SELECT aID, bID
    FROM tags
    WHERE placeID = "abc" AND tagID = "def";

    -- use the temp table somehow
    -- ...
    -- ...
    -- ...

    -- drop temporary table temp; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

because create temporary table if not exists temp is flakey

General Comments

One should not embark into writing stored procs until somewhat fluent on the simple topic of DELIMITERS. Wrote about them in a section here called Delimiters. Just hoping to head you off from unnecessary wasted time on such a simple thing, than can waste a lot of debugging time.

Also, here in your question, as well as in that reference, keep in mind that the creation of tables is DDL that can have a large percentage of the overall profiling (performance). It slows down a proc versus using a pre-existing table. One might think the call is instantaneous, but it is not. As such, for performance, using a pre-existing table with ones results put into their own segmented rowId is much faster than enduring DDL overhead.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • So maybe the problem is how i'm doing this? So I'm new to this so I test my queries in myphpadmin. So I'm just running this in there. What is the code above? PHP? It's not something I can use in myphpadmin. – Stuart Durning Oct 31 '15 at 18:21
  • nah, straight sql, no PHP. It is called a [Stored Proc](https://dev.mysql.com/doc/refman/5.0/en/create-procedure.html), embedded in your db, and you call it with `call` – Drew Oct 31 '15 at 18:24
  • so your task is to put something worthwhile in the `use the temp table somehow` section :) – Drew Oct 31 '15 at 18:27
  • I can't run it in myphpadmin so something I'm doing isn't the same as what you're doing.... – Stuart Durning Oct 31 '15 at 18:28
  • note sure, depends on your version, see [this](http://stackoverflow.com/a/12395667). I don't use that stuff, boss. Too limiting :) – Drew Oct 31 '15 at 18:30
  • Thanks for all the input @Drew. I think I am just going to post the full version of what I'm trying to do rather than a snippet, as I haven't seen anybody say my original code is wrong (other than drop table which isn't the issue). If I post the full PHP people will be able to work from the same scenario. – Stuart Durning Oct 31 '15 at 23:45
  • so looking at yours and a couple of answers on here say I don't need the SELECT INTO statement to add the data into the temp table. I've done that and that works if I run in myphpadmin (although for some reason shows a warning). So thanks for suggesting that :-) So now I'm trying to use the data with PHP, it runs with no errors I can see but no data either. Should the below be OK? Sorry don't know how to submit formatted code in here.... ` $sql_ideas = 'drop temporary....create tempoary....use temp table';` `$result = $conn->query($sql_ideas);` – Stuart Durning Nov 01 '15 at 12:18
  • php has [multi-query](http://php.net/manual/en/mysqli.multi-query.php). Problem you are facing is most likely that poof the temp table vanishes. So in that case, (1) multi-query to keep it alive til done temp table that is, or (2) stored proc, or (3) a non-temp table as in a regular one – Drew Nov 01 '15 at 12:35
  • if you look in my Answer under the General Comments section where it says **DDL**: You would be much better served having a permanent table with an autoinc PK call it **id**, then do your **insert into** to get a **session #** if you want to call it that. Then in a second table you use that session number to do the insert you originally wanted to to, but in a second permanent table. It will not endure the DDL overhead of create temp table (as trivial as us mortals think it may be). The table won't vanish so not error prone. When done if you want, do delete from. Totally safe, fast – Drew Nov 01 '15 at 12:43
  • so I am suggesting two new permanent tables. The reason for two is that in the first you merely want a unique number coming back. In the second table you use that unique number in all your rows because your insert/select proposed is going to have more than one row (most likely) – Drew Nov 01 '15 at 12:46