asked
Build history table based on two tables
I have three tables: 1) Core 2) Stage 3) History I need to compare core table vs Stage table. What ever the new data (new row or updated row) in stage table will go into core table either by insert or by update. If exist same row do not update.Keep it. Before updating core table we need to move that historical row to history table. Table script is same for all. For instance: Create table Core_table (C1 int Primary key Clustered,C2 varchar(10) ,C3 decimal (16,6),C4 Date) Core table C1 C2 C3 C4 1 Ron 1.2 1950-02-21 2 Sham 2.4 1960-03-01 3 Joe 3.9 1980-04-03 4 Rama 5.9 1999-01-01 Stage table C1 C2 C3 C4 1 Ron 2.2 1950-02-21 2 Sham 2.4 1960-03-01 3 Joe 3.9 1980-04-09 4 Ram 5.9 1999-01-01 5 Tom 6.2 1968-01-29 Final Output: Core table data C1 C2 C3 C4 1 Ron 2.2 1950-02-21 2 Sham 2.4 1960-03-01 3 Joe 3.9 1980-04-09 4 Ram 5.9 1999-01-01 5 Tom 6.2 1968-01-29 History table C1 C2 C3 C4 1 Ron 1.2 1950-02-21 3 Joe 3.9 1980-04-03 4 Rama 5.9 1999-01-01 Please help to resolve this issue. Thanks, Ronak
You'll have to do this in two steps. First, you can use the MERGE operation to update/insert data from the first table into the second. Then you can do the same for the history table (I'm assuming same rules, if it doesn't exist in history, move it there). Read up on how to implement the [MERGE statement here][1]. [1]:
http://msdn.microsoft.com/en-us/library/bb510625.aspx
13 People are following this question.
