Temporary Table error when executing entirety of script

temporary-tables

I'm having a problem with running the following script in it's entirety – That is, if I run it line-by-line separately it appears to work, but if I run everything at once, I get the error Message "There is already an object named '#customers' in the database." Any ideas as to why this happens?

if object_id('tempdb..#customers') is not null DROP TABLE #customers
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage

SELECT [customer_number]
  INTO #customers 
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage 
  FROM #customers 

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

SELECT * 
 INTO #customers
 FROM #temporary_storage

Best Answer

if object_id('tempdb..#customers') is not null DROP TABLE #customers
GO
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage
GO

SELECT [customer_number]
  INTO #customers 
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage 
  FROM #customers 

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

GO --- End Batch here

SELECT * 
 INTO #customers
 FROM #temporary_storage

You can't put them in one batch, you have to put a GO between the drop statements.

Related Solutions

Oracle 11g – Using Temporary Table Inside Procedure

It depends on why you are creating the temporary tables in MySQL.

Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data. Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else. If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.

Assuming that you really need a temporary copy of the data, you can create global temporary tables. You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code. The global temporary table ensures that each session can only see the data that session has inserted. The only difference is that you're not dropping and recreating the structure of the table inside your procedure.

Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table. PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously. You can do something like

DECLARE
  TYPE emp_tbl IS TABLE OF emp%rowtype;
  l_emps emp_tbl;

  CURSOR emp_cur 
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Fetch 10 rows at a time from the cursor into the collection.
    -- You'd realistically want a larger limit, something between 100 and 1000 generally
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 10;

    EXIT WHEN l_emps.COUNT = 0;

    -- An example of manipulating the collection in memory
    FOR i IN 1 .. l_emps.COUNT
    LOOP
      l_emps(i).sal := l_emps(i).sal * 2;
    END LOOP;

    -- And an example of using the collection to update a table
    FORALL i IN 1 .. l_emps.COUNT
      UPDATE emp
         SET sal = l_emps(i).sal
       WHERE empno = l_emps(i).empno;
  END LOOP;
END;

Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.

PostgreSQL – Table Variable Not Recognized in Stored Procedure

There are quite a few problems with this code:

Consecutive calls of select ... into result do no accumulate rows into result, each one overwrites whatever was previously in result.
A variable typed after a table (result of type result_load_status here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE or a cursor if possible.
select * from name_of_a_variable does not exist as a valid construct. A temporary table instead of a variable's name would work.

Anyway since accumulating results is already built in RETURN QUERY, you don't need any of the above.

Quoting from the documentation:

RETURN QUERY appends the results of executing a query to the function's result set

So you may just write:

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;

without any need for the problematic result variable.

Best Answer

Related Solutions

Oracle 11g – Using Temporary Table Inside Procedure

PostgreSQL – Table Variable Not Recognized in Stored Procedure

Related Question