Mirror tables based on dates and join for reporting

[cfrench]

Please let me know if this is possible and a reasonable procedure. Thank you in advance.

I have a SQL Server table that we use strictly for reporting that is updated daily through a dts package and our legacy system. This table has gotten very large and slow to report on now that it has almost 3 years of data in it.

Can I pull the data out based on year and put into a separate identical table and have those tables "connected" for reporting purposes? Can I do this with multiple tables and have a process that will create a new table for, say, 2004 data, 2005 and so on? Would this help with the efficiency of the reporting environment?

How to's or suggetions would be greatly appreciated.

Thanks again.

 

[ClaireHCM]

This surely can be done.But it also depends on how your DTS package is written.You could use select * into NewTableName from OldTable where year(Date) = 2004 ....

 

[cfrench]

How could "connect" those tables?

 

[hneal98]

You can't connect them because there is no id in either table that would match the other since they are different data for different times.

What you could do is run a select for one table and put that into a temp table and then do a union and run a select for the other table as well. Here is an example.

select * into tblCombined from tbl2004 where 1=2

select * into tblCombined from tbl2004
UNION
select * from tbl2003

The first select creates a table that looks like tbl2004. Then the next lines gets the data from both tbl2004 and tbl2003 and dumps it into that table called tblCombined. Then all you have to do is base your report on tblCombined.

Hope that helps...

 

[SQLSister]

Whether this will help the efficient of the reporting environment depends on how you do your queries. If a large portion of them only concern the current fiscal year, it should tremendously improve those queries but slow the multi year ones.

If you are usually concerned with multi year trend data, then your current structure will be faster.

A union query is how you would combine the two. I wouldprobably use it as the basis for a view to make it easier to draw specific reports from the data. No need to create a whole extra table to store the data in.

 

[hneal98]

I agree. Thanks for the correction, SQLSister.

 

[cfrench]

Good point, thank you. I'm a novice programmer and will have to do some homework on union queries but I think i have an idea on how to get started.

 

[cfrench]

to increase efficiency, would i be better to break my table up into components?

Currently it is a sales reporting table that has customer name, order info, price/cost info, etc.

I could probably break it into 2 tables but i don't think it will save that much space...