Post reply

Views

  • July 7, 2003 at 12:46 pm

    #80521

    We are designing a new application. We wish to put 2 years of data in a main database and move everything older than 2 years into another history database. There will be a job that runs every night to transfer 1 day data from the main to history database. Most of the reports access 2 years of data. But it is possible that the reports could need more than 2 years of data. So the reports have to look at both the databases. I could create views with a UNION but I don't want to query both the databases when it is not needed. What is the best way to handle this situation?

    Any ideas are greatly appreciated

  • July 7, 2003 at 1:06 pm

    #464381

    Maybe use an IF statement.

    IF

    mydate > getdate()-730

    THEN

    SELECT <columns>

    FROM main_database

    ELSE

    SELECT <columns>

    FROM history_database_view

    The getdate()-730 would give the date 2 years (730 days) from today.

    -SQLBill

  • July 7, 2003 at 1:41 pm

    #464384

    Thanks for your time SQLBill. So I have to do this in every query, for every table. That is going to be a nightmare for the developers. Can I write the code you suggested in the view definition? That way, the developers can jsut use the view name not worrying about the if?

    Thanks

  • July 8, 2003 at 1:13 am

    #464409

    Look up Partitoned Views in Books Online, it does exactly what you want, (ie selectively reads data from either or both DB's according to the date span required)

    - HTH Neil

  • July 8, 2003 at 8:02 am

    #464510

    Neil- Someone else suggested the same to me. I will look into distributed views and get back to the forum. Thanks for the direction.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply