0

I want to move data from one table to another with same database using a stored procedure.

Each time stored procedure executes only updated columns are copied from one table to other in SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2099433
  • 33
  • 5
  • 4
    [What have **you** tried so far?](http://www.whathaveyoutried.com) - show us some effort on your part! Where are you stuck? – marc_s Feb 22 '13 at 12:48
  • only updated *columns* are copied or do you mean updated *rows* ? – jazzytomato Feb 22 '13 at 12:54
  • When/under what circumstances is the stored procedure being called? – Tim Feb 22 '13 at 13:00
  • I think I understand what you want to do. I would look into SQL Server Change Tracking. You can then write a stored procedure which queries the change data and copies the "updated" rows to a different table. You could also look into old-school timestamp columns, however, Change Tracking is better. If you used a trigger instead of a stored procedure, you wouldn't need either of these. – muhmud Feb 22 '13 at 14:04

1 Answers1

0

You can achieve this using audit columns.

On the source table you need to add a ModifiedOn datetime column. Any time a column is updated on the source table you also need to update the ModifiedOn column to getdate() to designate that row has been modified and a column value has been updated.

When you copy data from the source table to the destination table, if you keep track of the last ModifiedOn value you loaded to the destination table you can use that value to find rows on the source table that have a larger ModifiedOn value and then only load the rows that have had changes.

vvvv4d
  • 3,881
  • 1
  • 14
  • 18