October 13, 2008 at 4:29 am
I have a db with 12 tables for the each year (2005, 2006, 2007. 2008-till september) and I need to query every table (44 tables at all) and create a new table with the query results.
The example below works for the 2005/02 table (february/2005).
I need a stored procedure to
1 - change the year-table (from 2005 to 2008),
2 - in each year-table change the month (from 01 to 12) and
3 - in each month change the type (once "P" an once "I" for each month),
4 - make the Select and
5 - then create the new table.
Any help?
Thanks in advance
Renato
create table table_amc_2005_02_P
SELECT
table_amc_2005_02.cart_usu,
table_amc_2005_02.nome_usu,
table_amc_2005_02.data_realiz,
table_amc_2005_02.tipo_mov,
table_amc_2005_02.cod_proc_ins,
table_amc_2005_02.desc_proc_ins,
table_amc_2005_02.cod_prestador,
table_amc_2005_02.nome_prestador,
table_amc_2005_02.esp_prestador,
table_amc_2005_02.quant,
table_amc_2005_02.valor,
table_amc_2005_02.`mod`,
table_amc_2005_02.desc_mod,
table_amc_2005_02.nro_guia,
table_amc_2005_02.loc_atend,
table_amc_2005_02.desc_loc_atend,
table_amc_2005_02.`conv`,
table_amc_2005_02.desc_conv,
table_amc_2005_02.grau_depend,
table_amc_2005_02.data_nasc,
table_amc_2005_02.ano_comp,
table_amc_2005_02.mes_comp,
table_amc_2005_02.chave_unica,
table_amc_2005_02.ano_data_realiz,
table_amc_2005_02.mes_data_realiz
FROM
table_amc_2005_02
WHERE
table_amc_2005_02.type_mov = 'P'
October 13, 2008 at 6:47 pm
:)Hi there,
Uhmmmm... Why not use one table instead of 44?
Example:
Hope this helps... ^__^
Create Table MyTable
(
IDINTIDENTITYNOT NULL,
Item1VARCHAR(MAX)NOT NULL,
Item2VARCHAR(MAX)NOT NULL,
DateDATETIMENOT NULL
)
--Now, if we want to view a particular Year and Month, We just filter with the where clause
SELECT *
FROM MyTable
WHERE DATEPART(m,Date)=1--January
ANDDATEPART(y,Date)=2008
Please tell me if this post was helpful or needs some modifications... ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
October 13, 2008 at 7:02 pm
Is your end goal here to get the data into 1 table for the future, or simply to build a query that will read from these 44 (and ever growing) number of tables? Also, how large are these tables? Are they created manually, or made by some process. If it's not possible to combine them into 1 table and use that going forward(which seems like it would be the optimal thing to do), is it possible to pre-build the next several years worth of tables now?
October 25, 2008 at 7:47 pm
Don'cha just love the feedback on some of these posts? 😛
--Jeff Moden
October 25, 2008 at 8:08 pm
Yeah, especially the ones with one post who never even come back to see the answers they are given.
October 26, 2008 at 6:28 am
Thanks for you answer.
I think I need to be more clear.
I already have a single table for each month in each year.
I need to export each table to Excel for some users analyze them.
Some of these tables have more than 65.000 rows, what makes them impossible to open with Excel.
So, I really need to divide each month-table into 2 tables each with less than 65.000 rows.
Renato
October 26, 2008 at 9:15 am
Seems to me like you need to go with analysis services here and show them how to use it to run reports (on there own sandbox server). That way they'll get to do anything they want, no matter how much data is involved. Excel was not built to handle that much data... ever.
October 26, 2008 at 9:16 am
Still very limited on the information you are providing to us, but one way you could accomplish this is to use the built in database connection functions within Excel. There are two ways you can easily make use of this.
1. Build an excel sheet with a built in query that allows them to supply a month, year and range (such as lower or upper for first 60K or last 60K, this can be expanded if there are tables with more than 131K rows). This query should ensure that no more than 65K rows are returned at once. If you know the types of analysis they are going to be doing on this data, this workbook can be made to have all your pivot tables / analysis calculations pre-built into other worksheets. After they refresh the data, they just refresh the analysis sheets / pivot tables and they're good to go.
2. Build a separate excel sheet with prebuilt lookups for month / year / range for each one so that your users do not have to input criteria. For example, you'd have Jan08Begin.xls, Jan08End.xls, Feb08Begin.xls, etc.
I would personally go with the first approach. It takes a bit more user education, but it takes away the management aspect and gives you one thing to update if you ever want to change anything. I've found that people generally remember how to use the sheet after you show them once or twice, even if they're really low end users(assuming they use it on a semi regular basis anyways).
October 26, 2008 at 10:29 am
Upgrade to Excel 2007 - which can handle up to one million rows per sheet.
That would be the short term solution - the long term would be to normalize the database and use analysis services to provide your users the ability to perform these calculations themselves.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 26, 2008 at 7:25 pm
pozzo (10/26/2008)
Thanks for you answer.I think I need to be more clear.
I already have a single table for each month in each year.
I need to export each table to Excel for some users analyze them.
Some of these tables have more than 65.000 rows, what makes them impossible to open with Excel.
So, I really need to divide each month-table into 2 tables each with less than 65.000 rows.
Renato
No... you don't. What you really need to do is to provide the users with the answers they need. That means, no spreadsheet analysis. Have them define what the analysis should be and you provide it for them. 😉
--Jeff Moden
October 27, 2008 at 6:30 am
One other thought here is to utilize Views. If your goal is to limit data for the end user, then have one physical table with your data (which you can use in your stored procedures), but then create Views for the Excel users that have built in date filters.
October 27, 2008 at 9:03 am
Views! Good idea!
Thank you
October 27, 2008 at 10:25 am
Seems like a maintenance nightmare.
Why don’t you put all the data in one table and have your users limit what they get back in Microsoft query.
They have far more flexibility and you have a lot less grief.
For example if they wanted to only get data for the first day of the month for the last year.
Currently they would have to access a minimum of twelve tables and then merge all the data.
One table would be far more efficient for both you and your users.
David Weil
October 27, 2008 at 10:35 am
You could also bring all the tables together into one table using a partitioned table. You would just have to add a new partition each month. This way, you have one table and don't have keep adding new tables to views, stored procedures, or embedded queries in Excel.
Also, I agree with the other posts about doing the analysis up front for the users, either in Analysis Services or Reporting Services depending on the requirements.
😎
October 27, 2008 at 1:22 pm
Thank you, guys.
Now I have many options. I´ll select one and do my job.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply