Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: Question on automatic copying table from one datbase to another on same SQL Server

    Currently have two databases. A live database and a history database. Also have the ability to purge data from the live to the history database. What I'm looking for is the ability, if the table doesn't exist in history, to automatically create the table in the history database using the format of the table within the live database. Any help would be greatly appreciated. Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Log Shipping sounds like a plan

    [BOL] Log shipping

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Ruprect
    Log Shipping sounds like a plan

    [BOL] Log shipping
    Within the same server? Kinda overkill, isn't it?

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

    i do believe that you do know what i will suggest as his second option?

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    Originally posted by Ruprect
    live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

    i do believe that you do know what i will suggest as his second option?

    I figure if I have to an option would be to use the sys tables for the information and build the create table string on the fly but was hoping there was a quicker and easier way ^_^

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Ruprect
    live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

    i do believe that you do know what i will suggest as his second option?
    Hmmm, get a real job? OK, that was a joke

    Trx Replication? I don't know, still sounds too heavy. I'd drop the idea of having "live history" database residing on the same server. I'd worry about that one once a day, at midnight, after all backups are done.
    Last edited by rdjabarov; 04-06-04 at 16:39.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about INSERT INTO if the table exists, or SELECT INTO if the target table does not exist?

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i was gonna go with dts...
    a scheduled import export but this could be handled with a vbscript looking for the existence of the table and running one of two possible sub packages depending on the table's design
    the only problem is the fact that this doesnot rely on the actual table's schema. you would have to know that already.

    in the case of the actual question, i think that pat has hit on the easiest solution.

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    After you do the insert into, won't you still need to create the matching indexes and relationships, or are you not worried about that.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I would second Ruprect suggestion about using Log shipping and say using your own log shipping you can achieve the task.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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