question

Ronakshah112 avatar image
Ronakshah112 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
t-sqlmergedata compare
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.