SQLTeam.com | Weblogs | Forums

How to move records in realtime from one table to other based on a condition


#1

Hi,
I have a unique requirement where the data in the SQL table is overwritten with new values when they are entered on the front end and its a data transfer application limitation not to overwrite but i need to have all the historical data for a given unique ID.
How can the data be moved ( for some rows which meet the criteria) to be moved a different table in realtime so that all the historical values are stored in the new table.

The current table looks like

VID QueryID Response
V123 Q1 A1
V123 Q2 A2
V123 Q3 A3
V234 Q1 A4
V234 Q2 A5

when new values are added for existing VID and QueryID
|V123|Q1|A6|
|V234|Q2|A7|

The table values changes to
|VID|QueryID|Response|
|V123|Q1|A6|
|V123|Q2|A2|
|V123|Q3|A3|
|V234|Q1|A4|
|V234|Q2|A7|

but i would like to move this data to a new table where it stores every instance and only for those rows which has QueryID like Q1 and Q2 and store all the historical values as well and would like to do this in realtime.
Please suggest how this can be done.
Thanks,
|VID|QueryID|Response|
|V123|Q1|A1|
|V123|Q2|A2|
|V123|Q3|A3|
|V234|Q1|A4|
|V234|Q2|A5|
|V123|Q1|A6|
|V234|Q2|A7|


#2

If you're on SQL 2016 (or better 2017) look into Temporal Tables which make a lot of this work simpler and more efficient. If not, you're going to have to wrap the update logic in a stored proc or use a trigger.