Post reply

Data archive

  • August 2, 2010 at 1:55 am

    #227363

    hello experts,

    i have a database which has around 10 tables which stores log of different operations of application. as i said there are log tables so they grows frequently, some of these data i use in application also,

    actually i want to transfer some data of these table to a different database. after that i want to delete data from these tables, but each table shud have at least one month data. i how can accomplish that in a ssis package which i wud run every fortnightly and it will archive the data automatically, can any body suggest me

    thank in advace

  • August 2, 2010 at 4:08 am

    #1200898

    You can accomplish through the Execute SQL Task. You can write the custom sql script to meet your requirements.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 2, 2010 at 4:21 am

    #1200904

    can you suggest me what kind of sql script i'll have to write here

  • August 2, 2010 at 4:56 am

    #1200918

    1)actually i want to transfer some data of these table to a different database.

    2)after that i want to delete data from these tables, but each table shud have at least one month data.

    1) The T-SQL query which will insert the requisite data into the other database

    2) A delete statement which will delete the copied data from the source

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 2, 2010 at 6:00 am

    #1200940

    can you suggest me query to append data in existing table data

  • August 2, 2010 at 6:40 am

    #1200959

    Appending = Inserting

    Please go through the INSERT statement details on MSDN.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 2, 2010 at 7:04 am

    #1200979

    Going through you request, i found that Table Partitioning might do the trick for you.. Me, in my sytem, implemented this Partition for Archive data; which is maintaining only the latest set (we have specific mechanism on we define sets using "versions" each time a job inserts data into the Archive tables) of data for each month in one filegroup and then "moving" the rest to the some another filegroup which will be located on a different drive..

    If you are interested in this process, try going this awesome whitepaper from Kim Tripp : Partitioned Tables and Indexes in SQL Server 2005

    Hope this helps! 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply