在MS Access中将SELECT INTO改为INSERT INTO以追加数据的问题

No problem at all—let's adjust your query to append data to your existing Indices_Data table instead of creating a new temp2 table. Here's how to do it:

Step-by-Step Adjustment #

1. Replace SELECT * INTO temp2 FROM ( with INSERT INTO Indices_Data ([Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [R1], [R2], [ReturnSqrd], [CompoundReturnSqrd]) SELECT
2. Keep the rest of your subquery exactly as it is (since it generates all the required fields and calculated values).

Modified Query #

INSERT INTO Indices_Data ([Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [R1], [R2], [ReturnSqrd], [CompoundReturnSqrd])
SELECT TOP 60 
    [Date],
    [Security],
    [Close],
    [High],
    [Low],
    [Return],
    [CompoundReturn],
    [High]-[Low] as [R1],
    [R1]/(([High] + [Low])*.05) as R2,
    [Return]^2 as ReturnSqrd,
    [CompoundReturn]^2 as CompoundReturnSqrd
FROM Indices_WS 
-- Add any original WHERE/JOIN clauses you had here

Key Notes to Avoid Issues #

• Explicit Column Listing: Always specify target columns in INSERT INTO like we did. This prevents errors if Indices_Data's structure changes (e.g., new columns added) and ensures data goes into the correct fields.
• Data Type Matching: Double-check that each selected column matches the data type of its corresponding column in Indices_Data (e.g., R1 should be a numeric type that fits the difference between High and Low).
• TOP 60: The TOP 60 is retained here—confirm this is still the number of rows you want to append each time you run the query.

内容的提问来源于stack exchange，提问作者Hedgebox