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 advanceinsert
into anotherdb.anothertable
select *
from livetable
where data = 'new'|||insert
into anotherdb.anothertable
select *
from livetable
where data = 'new'

You funny

What the DDL look like?|||We've had a topic name change - ANSI SQL - Yay! :D|||You funnyi are oney tryna be hepful :angel:

i used all the available information in the oiriginal post!!|||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|||PatP: yes :)|||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?|||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).|||Backup tools are not standard SQL, of course...|||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