By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,020 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Inserting unique data from one table into another

100+
P: 119
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO AssetPrices
  2. SELECT *
  3. FROM LivePricing;
Any help would be greatly appreciated.
Feb 20 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,633
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:

Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. 'Avoid the dreaded About to Append and Can't Append prompts. Because of
  4. 'the Composite Primary Key, only uniques values based on [Date] and
  5. [Symbol] will be appended to AssetPrices:
  6.  
  7. DoCmd.SetWarnings False
  8.   MySQL = "INSERT INTO AssetPrices SELECT * FROM LivePricing;"
  9.   DoCmd.RunSQL MySQL
  10. DoCmd.SetWarnings True
Feb 21 '07 #2

100+
P: 119
Wonderful. Thank you.
Feb 21 '07 #3

MSeda
Expert 100+
P: 159
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


Expand|Select|Wrap|Line Numbers
  1. If isnull(dlookup(“RecordID”, “AssetPrices”, “[Symbol] = ‘” & me.symbol & “’ AND [Date] = #” & me.date & “#”)) Then
  2.  
  3. MySQL = "INSERT INTO AssetPrices SELECT * FROM LivePricing WHERE [Symbol] = ‘” & me.symbol & “’ AND [Date] = #” & me.date & “# ;"
  4.  
  5. Else
  6.  
  7. 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 & “# ;"
  8. End if
  9. 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.
Feb 21 '07 #4

NeoPa
Expert Mod 15k+
P: 31,485
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.
Expand|Select|Wrap|Line Numbers
  1. 'SaveHist saves new records to the AssetPrices table.
  2. Public Sub SaveHist()
  3.     Dim strSQL As String
  4.  
  5.     Call DoCmd.SetWarnings(False)
  6.     'This SQL may fail on a large number of records.
  7.     'That's to be expected and shouldn't be seen as a problem.
  8.     strSQL = "INSERT INTO AssetPrices " & _
  9.              "([Symbol],[Date]) " & _
  10.              "SELECT DISTINCT [Symbol],[Date] " & _
  11.              "FROM LivePricing"
  12.              'If only todays data to be included then
  13.              'replace line above with the two below.
  14.              '"FROM LivePricing " & _
  15.              '"WHERE [Date]=Date()"
  16.     Call DoCmd.RunSQL(strSQL)
  17.     strSQL = "UPDATE AssetPrices AS AP " & _
  18.              "       INNER JOIN LivePricing AS LP " & _
  19.              "       ON (AP.Symbol=LP.Symbol) " & _
  20.              "       AND (AP.Date=LP.Date) " & _
  21.              "SET AP.Price=LP.Price " & _
  22.              "WHERE (AP.Price < LP.Price)"
  23.     Call DoCmd.RunSQL(strSQL)
  24.     Call DoCmd.SetWarnings(True)
  25. End Sub
Feb 23 '07 #5

100+
P: 119
Great. Thanks, both of you.
Mar 7 '07 #6

NeoPa
Expert Mod 15k+
P: 31,485
Not a problem :)
Did that work for you then? All understood etc?
Mar 7 '07 #7

100+
P: 119
It certainly did!
Mar 9 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.