Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    24

    Unanswered: Queries in productions AND history tables

    I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?

    Thanks,
    Whill96205

    * Also, any good resources I could use for developing stored procedures?
    Last edited by whill96205; 06-18-04 at 15:59. Reason: Forgot to add something...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that would be a VIEW with a union

    And here's a fundamental question...do you have an AD_DT column in all your tables

    How big is the database?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    UNION queries can be a little bit tricky, but are not too difficult if both the tables you are pulling from have the same structure. Don't be intimidated.

    Select [Column1], [Column2], [Column3]....from CurrentTable
    UNION
    Select [Column1], [Column2], [Column3]....from HistoryTable

    Not so tough, eh?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Posts
    24
    Thanks Brett and Blindman. I guess there's no big mystery for unioning then.


    Quote Originally Posted by Brett Kaiser
    Well that would be a VIEW with a union

    And here's a fundamental question...do you have an AD_DT column in all your tables

    How big is the database?
    Um... No. What is an AD_DT column?

    --whill

    P.S. btw, I'm using SQL Server 2000, not 7.0, if that makes any difference.

  5. #5
    Join Date
    Jun 2004
    Posts
    24
    Oh - almost forgot. Regarding database size... The database currently has approximately 43000 records (transactions) in it, increasing by about 100 per day. My task is to incorporate a bunch of related data which the client is currently tracking in several huge spreadsheets. This may nearly double the amount of data, but not necessarily the number of transactions. So I am redesigning the db table structure to better accomdate and utilize the new data that will be imported from the spreadsheets.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •