Unusual Error in Append Query

[Guest]

INSERT INTO [BACKORDER_SNAPSHOT_8_10_Cleaned up] SELECT
[BACKORDER_REPORT_072907 to 080407].* FROM [BACKORDER_REPORT_072907 to
080407], [BACKORDER_SNAPSHOT_8_10_Cleaned up];

The above query runs for 15 -20 minutes then returns error 3183 (Not enough
space on temporary disk.)

There is no temporary disk involved. The user has the mdb on her local
drive. It is not a split database, it is used by a QA tester to validated
data in another database.
(that is why the naming so so ugly)

A previous version worked well, but there were some name changes in the
mainframe database.

The import also uses an import spec.

 

[RoyVidar]

INSERT INTO [BACKORDER_SNAPSHOT_8_10_Cleaned up] SELECT
[BACKORDER_REPORT_072907 to 080407].* FROM [BACKORDER_REPORT_072907 to
080407], [BACKORDER_SNAPSHOT_8_10_Cleaned up];

The above query runs for 15 -20 minutes then returns error 3183 (Not enough
space on temporary disk.)

There is no temporary disk involved. The user has the mdb on her local
drive. It is not a split database, it is used by a QA tester to validated
data in another database.
(that is why the naming so so ugly)

A previous version worked well, but there were some name changes in the
mainframe database.

The import also uses an import spec.

Queries and SQL is one of the things I struggle with, so I might be
totally off - but do I understand this right, assuming shorter names

INSERT INTO BS
SELECT *
FROM BR, BS

BS - [BACKORDER_SNAPSHOT_8_10_Cleaned up]
BR - [BACKORDER_REPORT_072907 to 080407]

Doesn't this create a cartesian product (using "old style" listing of
tables, but no WHERE clause)?

If you need to join them, perhaps use INNER JOIN syntax, if not, perhaps
remove the [BACKORDER_SNAPSHOT_8_10_Cleaned up] query or table from the
FROM clause.

 

[John Spencer]

You do realize that you have a cartesian join in the select clause, so that
you could be generating a LOT of records and are filling up the temporary
work file that would get created and saved in the temp folder

INSERT INTO [BACKORDER_SNAPSHOT_8_10_Cleaned up]
SELECT [BACKORDER_REPORT_072907 to 080407].*
FROM [BACKORDER_REPORT_072907 to 080407]
, [BACKORDER_SNAPSHOT_8_10_Cleaned up];

If [BACKORDER_REPORT_072907 to 080407] contains 500 records and
[BACKORDER_SNAPSHOT_8_10_Cleaned up] contains 500 records you would be
trying to add
250,000 records to the [BACKORDER_SNAPSHOT_8_10_Cleaned up] table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Yes, thanks. I sent it on without looking.
My wife sent it from work.

Now I see the problem, thanks.
--
Dave Hargis, Microsoft Access MVP

You do realize that you have a cartesian join in the select clause, so that
you could be generating a LOT of records and are filling up the temporary
work file that would get created and saved in the temp folder

INSERT INTO [BACKORDER_SNAPSHOT_8_10_Cleaned up]
SELECT [BACKORDER_REPORT_072907 to 080407].*
FROM [BACKORDER_REPORT_072907 to 080407]
, [BACKORDER_SNAPSHOT_8_10_Cleaned up];

If [BACKORDER_REPORT_072907 to 080407] contains 500 records and
[BACKORDER_SNAPSHOT_8_10_Cleaned up] contains 500 records you would be
trying to add
250,000 records to the [BACKORDER_SNAPSHOT_8_10_Cleaned up] table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

INSERT INTO [BACKORDER_SNAPSHOT_8_10_Cleaned up] SELECT
[BACKORDER_REPORT_072907 to 080407].* FROM [BACKORDER_REPORT_072907 to
080407], [BACKORDER_SNAPSHOT_8_10_Cleaned up];

The above query runs for 15 -20 minutes then returns error 3183 (Not
enough
space on temporary disk.)

There is no temporary disk involved. The user has the mdb on her local
drive. It is not a split database, it is used by a QA tester to validated
data in another database.
(that is why the naming so so ugly)

A previous version worked well, but there were some name changes in the
mainframe database.

The import also uses an import spec.