Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
changing database dynamically

Răspuns propus changing database dynamically

  • Wednesday, February 13, 2013 4:20 PM
     
     
    Sign In to Vote
    0

    How can I change the database dynamically  in the query based on the date?

    We have following 13 database with same table in each database.

    Current- Month to date data

    M01-End of month data for Last Jan

    M02-End of month data for Last Feb

    M03—Last March

    M04—Last April

    ---

    M12---last December

    All these data base have same table1 ( Account int , Status  ( value can be either ‘Y’ or ‘N’)

    Each day I need to get all the account from current database  and get status code for last 8 months

    For Eg.

    Account    8 month trend ( as string for each month)

    123            YYNYNYYY- this is picked up from table1 from different database.

    To get 8 month trend, let’s say we want to run the report today

    It should pick the status code  from Current,M01, M12, ,M11, M10, M09,M08.M07

    If we run report in next month(march) it should  pick the status code from Current , M02,M01, M12,M11,M10,M09,M08

    please help

     

All Replies

  • Wednesday, February 13, 2013 4:32 PM
    Moderator
     
     Proposed
    Sign In to Vote
    1

    You can develop a stored procedure for the entire task.

    To loop through the database, you can use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    You can schedule the sproc as a SQL Server agent job:

    http://msdn.microsoft.com/en-us/library/ms186273.aspx

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Wednesday, February 13, 2013 4:38 PM
     
     
    Sign In to Vote
    0

    You can easily get the month out of a date, and you can also add or subtract months to that date. you can then append an M to your results, and use it in the form (between a while loop):

    set @var= 'insert into table (col1,col2)

    select col1,col2 from ['''+@dbname+'''].[schema].[tablename]'

    exec(@sql)

    But looking at your requirements, it seems to me that you need to re-check your requirements and implementation. Are you going to create a new db for each month? if so, what happens after 1 year?

     
  • Wednesday, February 13, 2013 4:43 PM
     
     
    Sign In to Vote
    0
    Thanks its very helpful.
     
  • Wednesday, February 13, 2013 4:43 PM
     
     
    Sign In to Vote
    0
     thanks-any time there is just 13 database- for current month and last 12 month.
     
  • Wednesday, February 13, 2013 10:00 PM
     
     
    Sign In to Vote
    0

    This poor man's partitioning is bound to incur some pain.

    The best I think is to set up partitioned views over the table. I have an introduction here:
    http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

    You would have to use dynamic SQL to set up the views, and maybe also add the constraints, but your queries would have to be polluted.

    How big are these databases?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se