Inserting unique data from one table into another

[billelev]

Hi,

I need to insert asset pricing data from one table that is updated multiple times daily, to another table containing historical data. The fields in both tables are:

Symbol, Date, Price.

However, I would like to avoid duplicate Symbol/Date combinations (i.e. in the historical pricing table only one price exists for a symbol on a particular date), ideally overwriting earlier pricing on the same day.

I think I need to use the INSERT command but with some additional 'overwrite if Symbol/Date already present' command. This is what I currently have, which as you can see, only inserts the data.

INSERT INTO AssetPrices
SELECT *
FROM LivePricing;

Any help would be greatly appreciated.

[ADezii]

Hi,

I need to insert asset pricing data from one table that is updated multiple times daily, to another table containing historical data. The fields in both tables are:

Symbol, Date, Price.

However, I would like to avoid duplicate Symbol/Date combinations (i.e. in the historical pricing table only one price exists for a symbol on a particular date), ideally overwriting earlier pricing on the same day.

I think I need to use the INSERT command but with some additional 'overwrite if Symbol/Date already present' command. This is what I currently have, which as you can see, only inserts the data.

INSERT INTO AssetPrices
SELECT *
FROM LivePricing;

Any help would be greatly appreciated.

On Table AssestPrices set a 2 Field Composite Primary Key on [Symbol] and
[DATE]. This will ensure a unique combination of values in these 2 Fields.
Place this code wherever appropriate:

Dim MySQL As String
 
'Avoid the dreaded About to Append and Can't Append prompts. Because of
'the Composite Primary Key, only uniques values based on [Date] and
[Symbol] will be appended to AssetPrices:
 
DoCmd.SetWarnings False
  MySQL = "INSERT INTO AssetPrices SELECT * FROM LivePricing;"
  DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

[billelev]

Wonderful. Thank you.

[MSeda]

I think it might suit you better if you used an if statement to either insert a record if there aren’t any for the listed date and symbol or Update the record with the latest value if it is present.
Try something like

If isnull(dlookup(“RecordID”, “AssetPrices”, “[Symbol] = ‘” & me.symbol & “’ AND [Date] = #” & me.date & “#”)) Then
 
MySQL = "INSERT INTO AssetPrices SELECT * FROM LivePricing WHERE [Symbol] = ‘” & me.symbol & “’ AND [Date] = #” & me.date & “# ;"
 
Else
 
MySQL =UPDATE AssetPrices INNER JOIN LivePricing ON (LivePricing.Symbol = AssetPrices.Symbol) SET AssetPrices.Price  = LivePricing.Price WHERE AssetPrices.Symbol = ‘” & me.symbol & “’ AND [Date] = #” & me.date & “# ;"
End if
Docmd.runsql mysql

I’m not good at writing SQl so it may need some tweeking, But what I’m trying to show you is that your executing either an insert or an update depending on whether the symbol and date combo are already present. Using only an insert statement and the sybol date as a primary key would prevent new records from with the same key from being inserted so there would be no duplicates, but it won’t update the existing price to the current one you need an update query to do that.

[NeoPa]

Using only an insert statement and the sybol date as a primary key would prevent new records from with the same key from being inserted so there would be no duplicates, but it won’t update the existing price to the current one you need an update query to do that.

That's absolutely right.
This is a concept that very few people get, and SQL doesn't have a built in structure for handling it :(
The routine below should do the job for you.

'SaveHist saves new records to the AssetPrices table.
Public Sub SaveHist()
    Dim strSQL As String
 
    Call DoCmd.SetWarnings(False)
    'This SQL may fail on a large number of records.
    'That's to be expected and shouldn't be seen as a problem.
    strSQL = "INSERT INTO AssetPrices " & _
             "([Symbol],[Date]) " & _
             "SELECT DISTINCT [Symbol],[Date] " & _
             "FROM LivePricing"
             'If only todays data to be included then
             'replace line above with the two below.
             '"FROM LivePricing " & _
             '"WHERE [Date]=Date()"
    Call DoCmd.RunSQL(strSQL)
    strSQL = "UPDATE AssetPrices AS AP " & _
             "       INNER JOIN LivePricing AS LP " & _
             "       ON (AP.Symbol=LP.Symbol) " & _
             "       AND (AP.Date=LP.Date) " & _
             "SET AP.Price=LP.Price " & _
             "WHERE (AP.Price < LP.Price)"
    Call DoCmd.RunSQL(strSQL)
    Call DoCmd.SetWarnings(True)
End Sub

[billelev]

Great. Thanks, both of you.

[NeoPa]

Not a problem :)
Did that work for you then? All understood etc?

[billelev]

It certainly did!