SQL Server Performance

Archiving of tabel data..

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Ritu, Mar 18, 2008.

  1. Ritu New Member

    We have table or Group of related tables, data of which to be archived into other database or table on some condition like can use Status columns (Completed) means All records with completed status should be moved to another table/database.
    Please advise me on best possible ways to perform this.
    Thanks in advance!!!
  2. FrankKalis Moderator

    I would create a stored procedure to do this and schedule it to run during off-peak hours.
  3. Ritu New Member

    Hi ,
    What will be the best approach to fetch the data from source tables :
    1) Fetch data from source table into temprary tables based on some filter criteria and then use temporary tables throught stored procedures.
    We can also define indexing in temporary tables so that it can help is fetching the data and inserting data in source tables in loop.
    2) Or we should use CTE's instead of Temporary tables to fetch data and do rest of things using CTE's.
    3) We can start a Snapshot Transaction level and then fetch the data directly from source tabels and insert into source tables.
    Rows could be 100000 at time from main table.
    If you have any other idea then please let me know.
  4. ghemant Moderator

  5. dineshasanka Moderator

    SSIS is also another option for. This will be handy if the databases lay around different servers

Share This Page