SQL Server Archive data

warrenaldo

Folks,

My DB is sql server.
Every year or so i want to do an automatic archive.
Archiving will only need to be done on one table - that stores all data.

What is the best way to do this - i am open to suggestions.

Heres what i thought.

I would create a new table [dbo.Archive] and every year or so i would fire a trigger that will grab the data out of the live Table and put it into the Archive Table. Deleting the Data from the Live Table.
I will do this via date calculations in SQL.

This will lead to my archive table getting large? But since its only archived data i dont see that as a problem - as no operations will be performed on it?

Can people give me any advice?

Tom Dunne

warrenaldo wrote:

My DB is sql server.
Every year or so i want to do an automatic archive.
Archiving will only need to be done on one table - that stores all data.

:eek: Ok, you didn't ask for help on DB design, so I won't say anything other than :eek:

warrenaldo wrote:

What is the best way to do this - i am open to suggestions.

It depends on whether you want the archived data to be online or offline, i.e. will users need to query data from last year, the year before, etc.

warrenaldo wrote:

I would create a new table [dbo.Archive] and every year or so i would fire a trigger that will grab the data out of the live Table and put it into the Archive Table. Deleting the Data from the Live Table.
I will do this via date calculations in SQL.

This will lead to my archive table getting large? But since its only archived data i dont see that as a problem - as no operations will be performed on it?

You wouldn't even need to go this far. It's been a while since I worked on SQL Server, but I am pretty sure you can do a simple query like this:

CREATE TABLE archive AS SELECT * FROM current_table

That can be set up as a job to run on a particular date. Not the most elegant solution, but it all depends on my previous question.

warrenaldo

Cheers Tom.

The design aint all that bad. Maybe not great. But i meant - there is only one table that needs archiving(NOT one table - that stores all data)

I tried your suggestion
CREATE TABLE archive AS SELECT * FROM current_table
but it doesnt seem to work. Incorrect syntax.
Im googling to find a way to do this tho. It would be brilliantly simpel. And perfect for my needs.

Any ideas

warrenaldo

Great stuff Tom - you pointed me in right direction.

in Oracle its :
create table archive as Select * from mytable;

But what i can do is:
Create table new_table like old_table;
Insert into new_table select * from old table;

BINGO - :-)

Tom Dunne

warrenaldo wrote:

in Oracle its :
create table archive as Select * from mytable;

You wouldn't think I'm an Oracle man, would you?

Glad to help. As I said, there are more elegant ways of doing this, but if that suits your situation, then that's all that matters.

Ginger

If you are using SQL Server, I would suggest looking at partitioning and such..

Also its just SELECT fields FROM T1 INTO T2

As the SELECT INTO creates the new table based on the query and the field defs..

Be aware that if the table exists already it might cause a problem...

Are you using SQL 2000 or 2005

Info here

http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx#_into_clause

Ginger

If its just historical data ship it to another database and make sure you update your stats etc if you delete the data from the original table, thats if you have indexes defined on your columns