Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.com... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a table for multiple sheets Excel 2007 | Excel Discussion (Misc queries) | |||
How to select different rows in Pivot table excel 2007 | Excel Discussion (Misc queries) | |||
What is a field name in excel when making a pivot table? | Excel Discussion (Misc queries) | |||
Making cells so users can select them | Excel Programming | |||
Making menu item changes temporary | Excel Programming |