Post reply

Stored Procedure to query many tables

  • October 13, 2008 at 4:29 am

    #199752

    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

    #884378

    :)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!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson

  • October 13, 2008 at 7:02 pm

    #884382

    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?

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 25, 2008 at 7:47 pm

    #889703

    Don'cha just love the feedback on some of these posts? 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 25, 2008 at 8:08 pm

    #889707

    Yeah, especially the ones with one post who never even come back to see the answers they are given.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 26, 2008 at 6:28 am

    #889744

    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

    #889750

    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

    #889751

    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).

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 26, 2008 at 10:29 am

    #889759

    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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 26, 2008 at 7:25 pm

    #889809

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 27, 2008 at 6:30 am

    #889933

    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

    #890041

    Views! Good idea!

    Thank you

  • October 27, 2008 at 10:25 am

    #890096

    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

    #890103

    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

    #890209

    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