Wiki Home

SQL - Temporary Table


Namespace: WIN_COM_API

09/27/2002 Steven Blake - My front end Microsoft® SQL Server™ application (VFP6.0) had a List Box with the Multi Select Property set true. Once the user made selection(s), the joins on the local workstation took forever because several remote tables each had hundred thousand rows to retrieve. I needed to perform the join on the SQL server and return only the result set.

The answer was to build and then fill temporary tables on the SQL server. SQL temporary tables have a single number sign (#) as the first character of their names. They are visible only to the current connection. Your multiuser code may designate a hard-coded table name and it will not conflict with other users creating temporary tables within the same application. The temporary tables are deleted when the user disconnects or are specifically dropped by your code.

You can not call SQL stored procedures to create a temporary table in FoxPro. Despite maintaining connection, the temporary table is dropped at the end of a SQL stored procedure. However, when you create the temporary table with SQL Pass - Through, you may then call SQL stored procedures which not only recognize the temporary table but also will not drop it at completion of task.
NOTE: When creating temp tables they are written into TEMPDB. On a system with a large number of users this can cause performance problems.

****************************************************************
*  Purpose: Create a SQL temporary table
*   Notice: FoxPro passthrough establishes table; same operation in
*           SQL stored proc drops table at end of proc
****************************************************************
SqlTask = "create table #foxprotest (gist CHAR(15))"
nResult = SQLEXEC(1, SqlTask)

Now that you've established the temporary table for the SQL connection, you may fill it row by row with either SQL Pass - Through or SQL stored procs. Then join the temporary table with permanent tables for your result set.
****************************************************************
*  Purpose: Insert rows into the established SQL temporary table
*   Notice: FoxPro passthrough
****************************************************************
SqlTask = "insert into #foxprotest values ('This works!')"
nResult = SQLEXEC(1, SqlTask)

****************************************************************
*  Purpose: Insert rows into the established SQL temporary table
*   Notice: Microsoft® SQL Server™ stored proc behaves
****************************************************************
SqlTask = "execute prg_TestThis 'This works!'"
nResult = SQLEXEC(1, SqlTask)

****************************************************************
*   Notice: The SQL stored procedure
****************************************************************
CREATE PROCEDURE prg_TestThis
   @somewords CHAR(15)
AS
-- Create a SQL temporary table if one does not yet exist but
-- its life span for a FoxPro app ends at the close of this proc
IF OBJECT_ID('tempdb..#foxprotest') IS NULL
   BEGIN
      CREATE TABLE #foxprotest ( gist CHAR(16) )
   END

-- Add a row of data
INSERT INTO #foxprotest VALUES ( @somewords )

We use temporary tables with Sql Server in a very complicated report process. Specifically dropping them before a second pass was a problem for us when we upgraded from Sql Server 7 to 2000.

We ended up using a clean up routine after each report run that:

1. Calls sp_tables to get all the # tables known. Only works if you have DataReader in tempdb turned on.
use tempdb;exec sp_tables '#Calc%', @table_type="'TABLE'";use mc_main_exl

2. Drop the tables if 'object_id' returns a NOT NULL on each # table returned from 1 above.
IF object_id('tempdb..#Calctable') IS NOT NULL
DROP TABLE #Calctable

Fox code not shown. Joe Kuhn
SQL tables beginning with two hashes '##' are called Global temporary tables. The only difference against one hash tables is they are visible to all connections. 10/14/2002 Steven Blake - Since double hash temporary tables are accessible by all users, a unique name scheme would have to be implemented. I can't dispute it, but I haven't seen the problem described in the next paragraph.

And one observation: One hash sql temporary tables do not work properly sometimes (SQL Server 7). In our case we have been creating table #VisitList in SELECT INTO command and the result was unpredictable. The work around was to use Global temporary table ##VisitList...

Pavel Celba
On a recent project I worked on, we arrived at a solution to the "look up table" problem that at first seems unduly complex, but after initial implementation was found to be much easier to maintain and expand. We had a nightly process that ran on the lan file server that pulled down all code/description fields from all look-up tables on the database server that had date/time stamps newer than the previous download. Then the cursors were copied into arrays. Since look-up tables are typically small, this approach was feasable. We had about 50 or so look-up tables. Most of them had less than 100 records, although one of them had several thousand. After the arrays were created (the array variable names all started with "ga", we saved them to a file called arrays.mem on the lan file server. It was about 500k in size. In the morning, when the users logged in, the application would compare the date/time of the local arrays.mem on the workstation with the one on the server lan file server. Since look-up tables change relatively infrequently, the .mem file would only have to be copied to the local workstation when it had changed on the lan file server. The download took about 1 second per workstation. Then, the application did a "restore from arrays.mem". This created all the look-up arrays. We had created a list box class that had intelligence about the arrays. It was quite easy to instantiate one of these list boxes onto a form and set a few properties to indicate the source of data (one of the arrays). Similar code was used in grid columns and anywhere else a code to description translation was needed that typically required look-up table access. This technique reduced lan and back-end server traffic IMMENSELY!
The main disadvantage was that if a look-up table on the database server was changed, we either had to wait until the next day to see the change, or ask everyone to exit the application, run the database server to lan file server download, create a new arrays.mem (a few seconds) and then ask the users to log in again. This only happened when a look-up table change was so critical that it needed immediate visibility to the workgroup. A very unusual situation in our particular environment. Of course, I have left out several details (some probably critical --- my apologies in advance).
Ray Kirk
( Topic last updated: 2002.10.15 10:06:07 PM )