CREATE TEMPORARY TABLE

On 5 Jun 2006 00:21:56 -0700, "Jamie Collins"
wrote:




I don't understand. When I've used a temp table in SQL Server I've
created it (using CREATE TABLE), loaded it (e.g. using INSERT INTO
VALUES) then used it to operate on other tables (e.g. JOIN). I don't
see how that is equivalent to your description e.g. you cannot JOIN a
base table to a recordset.

For me, the major feature of a temp table is not that it will be
"automatically deleted at the the end of a session/connection", rather
that it is not *available* to any other session/connection (I'd still
explicitly DROP TABLE when I'm done); perhaps the same effect could be
achieved in Access via permissions? I really don't know.


I've simulated that behaviour in a split FE/BE Access environment by
creating a third MDB file on the local workstation, and creating the
temporary tables within that database. Since the MDB file is local to
the user's machine, it is unavailable to any other users.

I think that trying to accomplish the same result with permissions
would be difficult because all users would see the same-named table,
and you would need to populate a user-specific field within the temp
table in order to differentiate between users. Creating a new MDB file
on their computer automatically differentiates between users.
**********************jackmacMACdonald@telusTELUS. net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

"Jamie Collins" wrote in message
ups.com...

Pat Hartman(MVP) wrote:
Access queries are the equivalent of temp tables in SQL Server.

I would say the that Access/Jet Query objects are equivalent to VIEWs
in SQL Server (or any other DBMS). Consider that Access/Jet's CREATE
VIEW syntax
(http://office.microsoft.com/en-us/as...322231033.aspx)
creates a Query object in the database.

you can open a recordset based on a query
and that is the equivalent of creating a temp table and then opening a
query
based on the temp table in SQL Server.

I don't understand. When I've used a temp table in SQL Server I've
created it (using CREATE TABLE), loaded it (e.g. using INSERT INTO
VALUES) then used it to operate on other tables (e.g. JOIN). I don't
see how that is equivalent to your description e.g. you cannot JOIN a
base table to a recordset.

For me, the major feature of a temp table is not that it will be
"automatically deleted at the the end of a session/connection", rather
that it is not *available* to any other session/connection (I'd still
explicitly DROP TABLE when I'm done); perhaps the same effect could be
achieved in Access via permissions? I really don't know.

Jamie.

--

Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

As I said, the closest equivalent to an Access query in SQL Server is a
VIEW. The only thing I can think that you can do in an Access query
that you can't do in a SQL Server VIEW is use an ORDER BY clause. Is
that what you mean? (I don't see how this unrelational feature of
Access would be useful, though.)

Otherwise, can you please post an example of something that cannot be
achieved with a SQL Server VIEW, hence necessitates a temp table, yet
can be achieved using queries in Access.

TIA,
Jamie.

--

Duane Hookom wrote:
I'm sure Help was referring to SQL Server tables in an ADP, not JET in an
MDB.

Actually, I think the OP has a point here. I don't agree with the
suggestions that this syntax relates to SQL Server or an ADP.

Take another look at the help:

http://office.microsoft.com/en-us/as...322201033.aspx

[quote]

CREATE TABLE Statement Help
Assistance Access 2003 Creating and Working with Databases and
Objects Database Objects Queries Microsoft Jet SQL Reference
Data Definition Language

Creates a new table.

Note The Microsoft Jet database engine does not support the use of
CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet
database engine databases. Use the DAO Create methods instead.

Syntax
CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH
COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL]
[index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

When a TEMPORARY table is created it is visible only within the session
in which it was created. It is automatically deleted when the session
is terminated. Temporary tables can be accessed by more than one user.

[unquote]

1) It appears in the Microsoft Jet SQL Reference section of the help;
Access Projects has its own section.

2) It explicitly states Jet does not support the use of CREATE TABLE
for non-Jet engine databases.

3) The syntax includes WITH COMPRESSION which only applies to Jet
engine databases. What would be the point of mixing Jet and non-Jet
syntax?

4) SQL Server's CREATE TABLE syntax does not include the TEMPORARY
keyword; rather, whether a SQL Server table will be created as
temporary is dependent on its own table naming convention:

[quote]

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { column_definition ...

Prefix local temporary table names with single number sign
(#table_name), and prefix global temporary table names with a double
number sign (##table_name).

[unquote]

I think this is a good spot by the OP. No smoke without fire, so what's
going on here? My guess is there is some unexposed functionality in the
Jet engine that a diligent but uninformed technical author erroneously
included in the help.

Could one of the MVPs (who don't have me on killfile g) please ask
questions e.g. in the private Microsoft newgroups? Perhaps someone in
the Access team could be leant on to take a look in their 'private
copy' of the Jet source code?

Jamie.

--

Jamie Collins wrote:
Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

--

I really don't know where you are going with this. All I said was that when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

"Jamie Collins" wrote in message
ups.com...

Jamie Collins wrote:
Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

--

Pat

In principle, I agree with you. There's one circumstance that I've
found temp tables to be necessary, but perhaps I just don't know the
right way to go about it. Suppose the result of a query is
non-updateable for whatever reason. If I want to use that query's
output to update some other table, then I am out of luck. The only way
that I've found to solve the problem is to create a temp table from
the original query, then to use *that* table to update the table that
I wanted updated in the first place.

Is there a "generic" way to solve this problem without involving the
temp table?



On Fri, 9 Jun 2006 16:33:15 -0400, "Pat Hartman\(MVP\)"
wrote:

I really don't know where you are going with this. All I said was that when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

"Jamie Collins" wrote in message
oups.com...

Jamie Collins wrote:
Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

--


**********************jackmacMACdonald@telusTELUS. net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Yes, that is one use of temp tables. Again, most people run into this
because they are attempting to store aggregated data which should be
calculated as needed rather than stored.

The "generic" way to solve the problem of having aggregated data that you
want to use to update a table is exactly what you are already doing. Store
the aggregated data in a temp table. Then use the temp table to update the
permanent table.

"jacksonmacd" wrote in message
...
Pat

In principle, I agree with you. There's one circumstance that I've
found temp tables to be necessary, but perhaps I just don't know the
right way to go about it. Suppose the result of a query is
non-updateable for whatever reason. If I want to use that query's
output to update some other table, then I am out of luck. The only way
that I've found to solve the problem is to create a temp table from
the original query, then to use *that* table to update the table that
I wanted updated in the first place.

Is there a "generic" way to solve this problem without involving the
temp table?



On Fri, 9 Jun 2006 16:33:15 -0400, "Pat Hartman\(MVP\)"
wrote:

I really don't know where you are going with this. All I said was that
when
working with Access, you rarely need to use a temporary table. In most
cases, you can use a query instead. Yes Views are similar to queries
except
that views don't take parameters and I'm not sure that they are updatable.
Queries can take parameters and they are updatable in most cases. I find
that most people create temporary tables (which cause database bloat)
because they don't understand that they don't need to. When creating a
stored procedure, temp tables are used because queries can't be nested as
they can with Access so the methods used in the two environments are
different.

"Jamie Collins" wrote in message
roups.com...

Jamie Collins wrote:
Pat Hartman(MVP) wrote:
In an Access query, you can join queries to queries or queries to
tables.
If the queries are updatable, the join to the table will most likely
be
also.

In SQL Server, you create temp tables because you want/need to do the
processing in steps. Similar functionality can be accomplished in
Access by
nesting queries.

The only thing I can think [snip]

I've had a further though: perhaps you meant 'materializing' data in
this way:

Sub FakeTempTable()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

Dim rs
' Create query equivalent of a temp table
Set rs = .Execute( _
"SELECT QTags.ESCI, QTags.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe)" & _
" AS QTags;")
MsgBox rs.GetString(2, , , , "(null)")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Is this what you meant?

Jamie.

--


**********************jackmacMACdonald@telusTELUS. net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

On Sat, 10 Jun 2006 01:38:41 GMT, jacksonmacd
wrote:

If I want to use that query's
output to update some other table

Well... this operation should VERY rarely be necessary.

If you can calculate it at will (in the query's output), *WHY* store
it?


John W. Vinson[MVP]

On Sat, 10 Jun 2006 00:07:38 -0600, John Vinson
wrote:

On Sat, 10 Jun 2006 01:38:41 GMT, jacksonmacd
wrote:

If I want to use that query's
output to update some other table

Well... this operation should VERY rarely be necessary.

If you can calculate it at will (in the query's output), *WHY* store
it?


In the most recent case that I'm dealing with, the table to be updated
is a linked DBF file that is hardwired to a shapefile to be displayed
in a desktop mapping system. The mapping system has *no* ability to do
on-the-fly calculations, thus, pre-calculation and storage is
required.

I agree with the general rule for not storing intermediate results,
but there will always be exceptions. I guess in this case, the "... if
you can calculate it at will..." part isn't true!



John W. Vinson[MVP]
**********************jackmacMACdonald@telusTELUS. net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security