SQL - SELECT INTO making temporary Excel table

[Guest]

I want to make a temporary table in Excel memory to run SQL statements on.
Is this possible. I see examples of a Select into with results going into a
worksheet. Is it possible to create a temporary table that I could then run
futher select statements against? If so, how and what variable type is
defined "DIM"?

Thanks!

 

[Sharad Naik]

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere without
brackets]

The array need not be defined with DIM, it is created automatically with
above select statement.

Another way is to use connection object and recordset object as under:

Dim myConn As Connection, myRec As RecordSet
Set myConn = New Connection
Set myRec = New RecordSet
myConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[file-path w/o
brackets]"

Now use your one kind of select statement as under:-
myRec.Open "Your SELECT STATMENT -1 (Without INTO)" , myConn, _
adOpenKeyset, adLockOptimistic, adCmdText
So the select data is in myRec RecordSet (in memory)

When done with above and you need another SELECT statment:

Set myRec = Nothing
Set myRec = New RecordSet
myRec.Open "Your SELECT STATMENT -2 (Without INTO)" , myConn, _
adOpenKeyset, adLockOptimistic, adCmdText

and so on.

Sharad

 

[onedaywhen]

I want to make a temporary table in Excel memory to run SQL

statements on.

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere without
brackets]

The array need not be defined with DIM, it is created automatically with
above select statement.

I don't see how this could work. The OP is referring to Excel data, so
I would expect you answer to use Jet SQL syntax. I'm not aware of Jet
having an Array keyword (and how does the array move from SQL engine to
memory?) Jet has a SELECT..INTO..FROM syntax but the INTO must precede
the FROM clause. Your SQL looks more like FoxPro SQL. Even if it did
work, you can't use a SELECT on an in-memory array...

Another way is to use connection object and recordset object

I don't see how this works either. This creates an in-memory recordset
which can in turn be output as an in-memory array, however neither of
which can be subsequently queries using a SELECT statement, which is
what the OP asked for. ADO.NET has the concept of in-memory tables but
the same is not true for ADO classic, for which a recordset is
essentially a sequential cursor.

Jet's SELECT..INTO..FROM can be used to create a temporary Excel
workbook (.xls) on *disk* which can be subsequently queried then
Killed.

Jamie.

--

 

[Sharad Naik]

Thanks a lot Jamie for pointing this out, I indeed completely missed it out
that
I was mixing the ForPro SQL with Jet ; when replied.

Your suggestion of putting into temporary excel file is of course good and
very simple to use,
but I thought OP didn't want it either, since he must have considered adding
an temporary worksheet and deleting it when done.

He can't further query RecordSet using the SELECT statement, and that's
why I told him, create RecordSet based of required SELECT crieteria,
work with it when done, Set RecordSet to Nothing and build again
with next SELECT statement.

Sharad

I want to make a temporary table in Excel memory to run SQL

statements on.

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere without
brackets]

The array need not be defined with DIM, it is created automatically with
above select statement.

I don't see how this could work. The OP is referring to Excel data, so
I would expect you answer to use Jet SQL syntax. I'm not aware of Jet
having an Array keyword (and how does the array move from SQL engine to
memory?) Jet has a SELECT..INTO..FROM syntax but the INTO must precede
the FROM clause. Your SQL looks more like FoxPro SQL. Even if it did
work, you can't use a SELECT on an in-memory array...

Another way is to use connection object and recordset object

I don't see how this works either. This creates an in-memory recordset
which can in turn be output as an in-memory array, however neither of
which can be subsequently queries using a SELECT statement, which is
what the OP asked for. ADO.NET has the concept of in-memory tables but
the same is not true for ADO classic, for which a recordset is
essentially a sequential cursor.

Jet's SELECT..INTO..FROM can be used to create a temporary Excel
workbook (.xls) on *disk* which can be subsequently queried then
Killed.

Jamie.

 

[onedaywhen]

He can't further query RecordSet using the SELECT statement, and that's
why I told him, create RecordSet based of required SELECT crieteria,
work with it when done, Set RecordSet to Nothing and build again
with next SELECT statement.

An alternative to creating multiple recordsets in this way would be to
use the MSDataShape provider (coupled with a regular data provider) and
the SHAPE syntax to create a single hierarchical recordset (i.e. a
recordset of recordsets) in one hit.

I think we're guessing a bit here, though. Perhaps the OP would like to
outline or detail his needs. It could be that using the Recordset
object's Filter property would suffice, as this has the effect of
re-querying the recordset as
SELECT * FROM <recordset> WHERE <Filter>

Jamie.

--

 

[Guest]

Thanks for all the input. 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 worksheet 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 just don't know if it is possible to have a temporary table in memory from
an INTO statement. When I use SELECT...INTO...FROM I get an undefined object
error.

Thanks for the help.
Dave

 

[onedaywhen]

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

--

 

[Guest]

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

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