Montly archiving of tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Montly archiving of tables

Author

Topic

SQL.Newbie
Starting Member

3 Posts

Posted - 2014-03-27 : 04:42:31

Hi guys,

im very new to SQL and didn't work with SQL yet.
Still it's my job to solve a problem i can't on my own.
I hope you are willing to help me with that ;D.

The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productiv that keeps track of the current bookings.

I hope anyone does actualy understand what im trying to say ;D.
I would have access to the enterprise edition if that changes anything at all.

It would be GREAT if anyone could help me with that since im kinda stuck searching for this on the net. Guess thats mostly due to my own incompetence.

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-27 : 09:25:53

First, you are doing this on Microsoft SQL (T-SQL) and not Oracle etc?

Are you sure you need to store the achieved table in a different schema?

If so you need to create a schema this table will be apart of if you haven't already.

http://technet.microsoft.com/en-us/library/ms189462.aspx

Once you have a schema setup you can then decide how to archive. Can you post the table structure of the main table?

All the columns and their data types? If you expand the database in SSMS then expand the table right click and choose script to new query and post that.

How are you going to archive the data? Are you going to have a new table every time you archive a month off OR create one archive table and insert the new records into it each month?

Once you have archived the data, are you then going to delete it from the main table?

SQL.Newbie
Starting Member

3 Posts

Posted - 2014-03-27 : 11:56:02

Well thank you a lot. Im working on answering all your questions. The thing with the queries is i got like 40 or smth tables is the schema. First of all that might be a bit much and secondly would i have to get every query seperatly?

SQL.Newbie
Starting Member

3 Posts

Posted - 2014-03-28 : 05:08:09

Ok,

i did my best to answer your questions ;D.

First of all here is the structure of the supposed main table i hope it's the correct one:

-------------------------------------------

USE [Eurotrade_TEST]
GO

/****** Object: Table [poresy].[abrechnung] Script Date: 28.03.2014 09:59:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [poresy].[abrechnung](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[shopsid] [bigint] NULL,
[abrechnungsmonat] [nvarchar](6) NOT NULL,
[lfdnr] [decimal](5, 0) NOT NULL,
[mindestmiete] [decimal](10, 2) NOT NULL,
[monatspacht] [decimal](10, 2) NOT NULL,
[gesamtsummepachtnetto] [decimal](10, 2) NOT NULL,
[gesamtsummepachtbrutto] [decimal](10, 2) NOT NULL,
[ustbetrag] [decimal](10, 2) NOT NULL,
[taxrate] [decimal](5, 2) NOT NULL,
[bearbeiter] [nvarchar](50) NOT NULL,
[status] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[shopsid] ASC,
[abrechnungsmonat] ASC,
[lfdnr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ('') FOR [abrechnungsmonat]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [mindestmiete]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [monatspacht]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [gesamtsummepachtnetto]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [gesamtsummepachtbrutto]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [ustbetrag]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ((0)) FOR [taxrate]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ('') FOR [bearbeiter]
GO

ALTER TABLE [poresy].[abrechnung] ADD DEFAULT ('') FOR [status]
GO

ALTER TABLE [poresy].[abrechnung] WITH CHECK ADD FOREIGN KEY([shopsid])
REFERENCES [poresy].[shops] ([id])
ON DELETE CASCADE
GO

-----------------------------------

I think it's nuts but seemingly there should be a new schema for EVERY month. So each month a new schema named feburary_2014 or similar has to be created. Maybe i just don't know enough but it sounds a bit crazy.
The data has to be copied to the new schema including all the tables. And yes it has to be deleted from the maintable afterwards. Everything should be working on a still acessible system.

This is a bit much for me so I appreciate your help very much. I'm able to write some sql statements and thats about it and now i got some work to do haha.

Thank you guys in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources