CODEWITHSUNDEEP
sqlms-access
user7346517
user7346517

Reputation: 25

MS Access Append Query too big

I have a database with a large table of pricing data = EB. I want to append 45 days of pricing data to that table so I have written the query below. The query doesn't seem to run though as I get 2 GB warning and also memory warning. The EB table has 716,580 records in it and I have indexed on Symbol and Date.

Please help as I'm new to this. Thanks

INSERT INTO EB ( Symbol, Description, [Currency], UNITS, [Date], LOW, HIGH, [CLOSE], INDEX_MIDPOINT, VOLUME, BID, ASK, [OPEN], MEAN )
SELECT EB_hist.Symbol, EB_hist.Description, EB_hist.Currency, EB_hist.UOM, 
EB_hist.Date, EB_hist.[Low(l)], EB_hist.[High(h)], EB_hist.[Close(c)], 
EB_hist.[Index(u)], EB_hist.[Volume(v)], EB_hist.[Bid(b)], EB_hist.[Ask(a)], 
EB_hist.[Open(o)], EB_hist.[Mean(m)]
FROM EB, EB_hist
WHERE (((EB_hist.Date)>(select MAX([Date]) from EB)));

Upvotes: 0

Views: 501

Answers (1)

June7
June7

Reputation: 21389

There is no JOIN clause in the SELECT between EB and EB_hist. This results in a Cartesian relationship (every record of each table is associated with each record of other table) which is probably why the query hits 2GB limit. This is the limit for an entire Access db. Try removing EB from the SELECT (not the SELECT Max() subquery).

Advise no spaces or punctuation/special characters (underscore only exception) in names. Also, date, close, currency are reserved words, should not use reserved words as names.

Upvotes: 1

Related Questions