+ Reply to Thread
Results 1 to 8 of 8

SQL - SELECT INTO making temporary Excel table

  1. #1
    dave k
    Guest

    SQL - SELECT INTO making temporary Excel table

    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!

  2. #2
    Sharad Naik
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

    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

    "dave k" <[email protected]> wrote in message
    news:[email protected]...
    >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!




  3. #3
    onedaywhen
    Guest

    Re: SQL - SELECT INTO making temporary Excel table


    Sharad Naik wrote:
    > > 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.

    --


  4. #4
    Sharad Naik
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

    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


    "onedaywhen" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Sharad Naik wrote:
    >> > 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.
    >
    > --
    >




  5. #5
    onedaywhen
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

    Sharad Naik wrote:

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

    --


  6. #6
    dave k
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

    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" wrote:

    > Sharad Naik wrote:
    >
    > > 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.
    >
    > --
    >
    >


  7. #7
    onedaywhen
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

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

    --


  8. #8
    dave k
    Guest

    Re: SQL - SELECT INTO making temporary Excel table

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1