Re: SQL - SELECT INTO making temporary Excel table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: onedaywhen (jamiecollins_at_xsmail.com)
Date: 01/07/05 

Date: 7 Jan 2005 00:41:05 -0800

dave k wrote:
> Here is more information about what I want to do.
> Run a query with about 5 calculated fileds. Have this query create a

> temporary table in memory.
> Use this new temporary table to perform additional queries against
other
> tables (primarily INNER JOINs).
> I would prefer to keep the NEW table in memory and not in a work***
if
> possible.
> I would also prefer to have this new table vs. embedding the query
into the
> subsequent queries for speed and for subsequent ease.

I take it by 'embedding the query' you are referring to a derived table
e.g.

SELECT DerivedTable.DataCol
FROM (
SELECT Col1 * 10 AS KeyCol,
Col2 / 10 AS DataCol
FROM [Sheet1$]
) AS DerivedTable
INNER JOIN [Sheet2$] T2
ON DerivedTable.KeyCol = T2.Col1;

Before dismissing using the same derived table in each query, I suggest
you do some *actual* time testing, the SQL optimizer might be better
than you think. As for 'ease', well that's a lifestyle choice <g>.

FWIW you may not *need* the derived table e.g. the above example could
be re-written as

SELECT T1.Col2 / 10 AS DataCol
FROM [Sheet1$] AS T1
INNER JOIN [Sheet2$] T2
ON T1.Col1 * 10 = T2.Col1;

However, a reason for using a derived table *would* be ease of reuse
i.e. I can copy and paste the derived table code between queries (or
within the same query).

> I just don't know if it is possible to have a temporary table in
memory from
> an INTO statement.

I'm fairly sure it is not possible with Jet, which is after all
file-based. The new table in the INTO will be created in the current
Jet connection (which must be on disk somewhere) or the ODBC data
source if specified (and all my ODBC sources ultimately point to data
persisted on disk rather than in-memory).

Jamie. 

--
Quantcast