Results 1 to 11 of 11

Thread: Append Data

  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: Append Data

    Hi, i need some help as i'm not good with SQL. I have 2 databases n SQL and i'm trying to write a rountine were it inserts all the new data from a live table to another table in another database.

    Is there any way how to do this?

    Once i have figurered that bit out i need to run the rountine every night automaically.

    I would really appreciate if you have the answers for my questions

    Thankyou in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    insert 
      into anotherdb.anothertable
    select *
      from livetable
     where data = 'new'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    Code:
    insert 
      into anotherdb.anothertable
    select *
      from livetable
     where data = 'new'

    You funny

    What the DDL look like?
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    We've had a topic name change - ANSI SQL - Yay!
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    You funny
    i are oney tryna be hepful

    i used all the available information in the oiriginal post!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    R937: Is the "anotherdb" from your example code a user (as in Microsoft/Sybase), a schema (as in Oracle/DB2), a database (as in MySQL/Pervasive), or something different? I guess that my point is that your code as presented will do slightly different things, depending on which database implementation is used to execute it.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PatP: yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Rudy's query works in DB2 even if both tables reside in different databases (not only different schemas) - DB2 for z/OS, that is. ;-)

    The interesting thing is that ISO/IEC 9075:2003-2 is very, very vague about what a "database" actually is. This results from the fact that different DBMS vendors apply this term to different concepts (and DBMS vendors drive the standard).

    @Hemish: you should be a bit more specific what you mean. Do you refer to schemas/schemata or federation?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Instead of writing a query you might be better using the backup tools available for your particular database system. For MSSQL you can set up nightly backup jobs using the scheduling tool that is available. In MySQL you can use the Mysqldump utility (from cmd line) to run a backup, which you could schedule using a cronjob (if unix) or windows scheduler (if win).

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Backup tools are not standard SQL, of course...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by stolze
    Backup tools are not standard SQL, of course...
    Yeah, Standard SQL doesn't support backups (literally true, since backups are only supported through vendor extensions, the standard definitions don't provide any references to backups).

    -PatP

Posting Permissions

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