SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Montly archiving of tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQL.Newbie
Starting Member

3 Posts

Posted - 03/27/2014 :  04:42:31  Show Profile  Reply with Quote
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.


Edited by - SQL.Newbie on 03/27/2014 04:42:56

Robowski
Posting Yak Master

101 Posts

Posted - 03/27/2014 :  09:25:53  Show Profile  Reply with Quote
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?
Go to Top of Page

SQL.Newbie
Starting Member

3 Posts

Posted - 03/27/2014 :  11:56:02  Show Profile  Reply with Quote
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?
Go to Top of Page

SQL.Newbie
Starting Member

3 Posts

Posted - 03/28/2014 :  05:08:09  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000