Re: SQL - SELECT INTO making temporary Excel table

From: dave k (davek_at_discussions.microsoft.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 07:29:04 -0800

Thanks, I will just reuse the statement portion of the code for the later
queries. If I define the string portion I should be able to reuse it in
other places. That would address my concern if I need to change it I don't
want to have to change it in 10 places and miss one.

Thanks for the help. I will stop my temporary memory table quest and get on
with it.

Dave

"onedaywhen" wrote:

> 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.
>
> --
>
>