Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql server 2008 Archive table

Posted on 2013-06-07
4
265 Views
Last Modified: 2013-06-07
I have 2 tables that were created last year for a process that runs once each year. These tables need to be cleared before we run the process this year. I have been told we should "archive" the tables. How would I do this/ What would be the process?
0
Comment
Question by:qbjgqbjg
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39229893
if you are using sql 2008, the best option is to go for Partitioning by year. that way it is easy to moveinto another table or you can leave as it it. it wont affect performance of your query at all as it is on a different partition.

http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39230153
The easiest way would be to do this:

1. Script the table structure but make sure you also script its indexes. To do that you will go in MS and right click on the database name > Task  > Generate Scripts > Next > Select Script specific... > expand Tables node > check the table(s) you need to script > Next > choose Save scripts to a specific location and below Save to file and Single file per object(if you have multiple tables) - choose a location folder > click on Advance button > in the bottom section called Table/View options make sure you set to True indexes, triggers, Foreign Keys but NO DATA!> click OK > Next > Next > wait until done > click Finish

2. Rename the tables that you scripted. Right click on them > Rename > give them name like table_YYYY, or whatever seems proper to you.

3. Open the scripts generated in step 1 and run them.

Done.

I agree that the partitioning will be a nicer solution but if you've never done that I would recommend to first test it and only when you are sure how it's done to implement it.
0
 

Author Closing Comment

by:qbjgqbjg
ID: 39230170
Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Correct Thousand and decimal separator in sql server 2008 3 31
Addition to SQL for dynamic fields 6 47
TSQL convert date to string 4 35
Parse this column 6 27
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question