Append Data

Hemish · #1 (**permalink**) 08-02-07, 06:55

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

r937 · #2 (**permalink**) 08-02-07, 08:08

Code:

insert 
  into anotherdb.anothertable
select *
  from livetable
 where data = 'new'

Brett Kaiser · #3 (**permalink**) 08-02-07, 13:52

Quote:

Originally Posted by r937

Code:

insert 
  into anotherdb.anothertable
select *
  from livetable
 where data = 'new'

You funny

What the DDL look like?

gvee · #4 (**permalink**) 08-02-07, 13:54

We've had a topic name change - ANSI SQL - Yay!

r937 · #5 (**permalink**) 08-02-07, 13:57

Quote:

Originally Posted by Brett Kaiser

You funny

i are oney tryna be hepful

i used all the available information in the oiriginal post!!

Pat Phelan · #6 (**permalink**) 08-02-07, 22:57

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

r937 · #7 (**permalink**) 08-03-07, 00:17

PatP: yes

stolze · #8 (**permalink**) 08-03-07, 04:04

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?

aschk · #9 (**permalink**) 08-03-07, 04:24

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).

stolze · #10 (**permalink**) 08-05-07, 08:52

Backup tools are not standard SQL, of course...

Pat Phelan · #11 (**permalink**) 08-05-07, 13:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech