Re: Replication

On Wed, 29 Jul 1998 21:32:59 -0700, "Edward Villalovoz" <edwardv@jps.net> wrote: >Does anyone know what the best way to check one table in an informix >database, compare it with a table in another informix database, and if there >is new data in the first table, append only the new data to the second >table? And I need to do this from perl. If you have any sample code it >would be helpful. :) Thanks in advance. So you don't need to handle updates of one or the other table, only new rows? You can do as follows connected to the database where the table you want to insert into is located: table1 is the first table that may contain new data and is located on a database database db1 on database server server1. table2 is the table where you want to insert any new data from table1. select table1.primkey, table2.primkey as primkey2 from db1@server1:table1 as table1, outer table2 into temp tt_1; insert into table2select table1.* from tt_1, db1@server1:table1 as table1 where tt_1.primkey2 is null; This assumes both tables have the same columns and types. If that is not the case you have to fix the insert accordingly. The primary keys does of course have to be the same for both tables for this to be meaningfull at all. I don't know perl so I can't give you perl code, but it should be easy to wrap these statements into that language. Non of them return any data of course. It may be faster to select all data from table1 into a temporary table first. You could of course do this with Informix replication as well if you have a new engine that supports the version of replication where you can set it up to replicate individual tables. You could also create a trigger on table1 above that inserts every new row in another table as well. Then all you would have to do is insert those rows in table2 in the other database and delete them from the extra table. The above select/insert solution you can do with dirty read and no locking, while this solution would require that you lock the extra table while you do the insert and delete so you are sure you don't loose any rows. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)