SQL Server Performance

dynamic dts script

Discussion in 'SQL Server DTS-Related Questions' started by boutwater, Oct 24, 2003.

  1. boutwater Member

    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,
  2. ykchakri New Member

    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 "
  3. boutwater Member

    Thank you very much. What is the mi for in parenthesis? I appreciate your help,
  4. bambola New Member

    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.


Share This Page