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.
LVL 6
Uptime Legal SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
If the tables you mention are in SQL Server indeed and they have a row identifier(ID) like PKey I mena then you could use:

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..
1
Scott PletcherSenior DBACommented:
It's best if possible that the tables are clustered on key_col:

UPDATE tp
SET
    col1 = td.col1,
    col2 = td.col2,
    col3 = td.col3,
    lastdate = GETDATE()
FROM tablepermanent tp
INNER JOIN tabledaily td ON
    td.key_col = tp.key_col

INSERT INTO tablepermanent ( <col_list, ...> )
SELECT <col_list, ...>
FROM tabledaily td
WHERE
    NOT EXISTS(
        SELECT 1
        FROM tablepermanent tp
        WHERE tp.key_col = td.key_col
    )
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Uptime Legal SystemsAuthor Commented:
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.
0
Uptime Legal SystemsAuthor Commented:
Appreciate the framework there, this works great.  I should be able to adapt this to do what I need, very much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.