Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data archive Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 1:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:11 PM
Points: 208, Visits: 1,026
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
Post #962019
Posted Monday, August 2, 2010 4:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #962059
Posted Monday, August 2, 2010 4:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:11 PM
Points: 208, Visits: 1,026
can you suggest me what kind of sql script i'll have to write here
Post #962066
Posted Monday, August 2, 2010 4:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980

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
Post #962083
Posted Monday, August 2, 2010 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:11 PM
Points: 208, Visits: 1,026
can you suggest me query to append data in existing table data
Post #962108
Posted Monday, August 2, 2010 6:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
Appending = Inserting

Please go through the INSERT statement details on MSDN.


Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #962132
Posted Monday, August 2, 2010 7:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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!
Post #962156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse