SQL Server Performance Forum – Threads Archive

dynamic dts script

Hello,
I have two databases, a production db, and an history db. I want to keep the production db as lean as possible, and move everything that has changed in the last half hour to the history db. I have a column in a table in the production db that is a DateTimeLastTrans. Is there any way to write a script in dts so that when it executes every half hour, it only takes the rows that have been changed in the last half hour? What I would like to run is something where the where clause is where DateTimeLastTrans > [30 minutes ago]. Is there any way to write a script that dynamically figures out the time stamp for 30 minutes ago? Thanks for your help,
Ben

---

Since you are copying rows from only one table and since you have a column with the timestamp of the transaction, you can write a simple script to do this instead of DTS. Something like "insert into historydb..table select * from productiondb..table where datediff(mi,DateTimeLastTrans,getdate()) < 30 "

---

Thank you very much. What is the mi for in parenthesis? I appreciate your help,
Ben

---

The mi goes for minute. It’s the part of the date for which you want to calculate the difference. Check DATEDIFF in BOL for more details. Bambola.

---

]]>