Merge existing SQL rows with another table (daily)
I have a database that pulls information from a series of spreadsheets into a table for use in Access, but each day it removes the table and re-imports the data which means historical data gets lost. What I would like to do is move the data from the table created by the SSIS procedure and move it into another table to have something like 'tabledaily' and 'tablepermanent'. What would I need to do to have a process move the data from 'tabledaily' to 'tablepermanent' and re-write existing rows with the updated values and add new items, but retain the previous history? Bonus if it can write the last date of the merge to the 'tablepermanent' if a value existed in 'tabledaily'.
For example:
Day 1 -
tabledaily
A Alpha 1
B Beta 2
C Charlie 3
tablepermanent
A Alpha 1 09/16/2015
B Beta 2 09/16/2015
C Charlie 3 09/16/2015
Day 2 -
tabledaily
A Alpha 11
B Beta 22
D Delta 4
tablepermanent
A Alpha 11 09/17/2015
B Beta 22 09/17/2015
C Charlie 3 09/16/2015
D Delta 4 09/17/2015
Day 3 -
tabledaily
A Alpha 11
B Beta 23
D Delta 4
E Echo 5
tablepermanent
A Alpha 11 09/18/2015 (this date updated even though the value didn't change since it was in 'tabledaily')
B Beta 23 09/18/2015
C Charlie 3 09/16/2015
D Delta 4 09/18/2015
E Echo 5 09/18/2015
Any help is appreciated, please let me know if this is confusing or not enough detail. The actual tables have a lot more columns that this, but there is always a key column that can be used to compare that doesn't change.
For example:
Day 1 -
tabledaily
A Alpha 1
B Beta 2
C Charlie 3
tablepermanent
A Alpha 1 09/16/2015
B Beta 2 09/16/2015
C Charlie 3 09/16/2015
Day 2 -
tabledaily
A Alpha 11
B Beta 22
D Delta 4
tablepermanent
A Alpha 11 09/17/2015
B Beta 22 09/17/2015
C Charlie 3 09/16/2015
D Delta 4 09/17/2015
Day 3 -
tabledaily
A Alpha 11
B Beta 23
D Delta 4
E Echo 5
tablepermanent
A Alpha 11 09/18/2015 (this date updated even though the value didn't change since it was in 'tabledaily')
B Beta 23 09/18/2015
C Charlie 3 09/16/2015
D Delta 4 09/18/2015
E Echo 5 09/18/2015
Any help is appreciated, please let me know if this is confusing or not enough detail. The actual tables have a lot more columns that this, but there is always a key column that can be used to compare that doesn't change.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys, I won't have a chance to try it out until tomorrow but I'll respond with an update as soon as I can. Appreciate the responses.
ASKER
Appreciate the framework there, this works great. I should be able to adapt this to do what I need, very much appreciated.
1. Transactional Replication (push subscription) for instance so you don'e need to worry about code/data being out of sync.
2. You could have 3 triggers on parent 'tabledaily' table for INSERT, UPDATE, DELETE and populate a queue with the actual ID (PKKEY) of the row that was touched and the action like ('U','D','I' for instance) then process only this subset in a daily SQL scheduled job by parsing the queue and pushing to the destination table 'tablepermanent' the "action" as it was logged in order in the queue table.
Also if there are no 'text' like columns in SQL you could use the EXCEPT and INTERSECT functions on all columns in order to "merge" two tables data into one.
HTH..