Thread: Re: SQL Query Validate Records Multiple Tables - Help Needed

Re: SQL Query Validate Records Multiple Tables - Help Needed

From
Rodrigo De León
Date:
On May 15, 8:17 am, Paul251 <savethe...@gmail.com> wrote:
> Hello...
>
> I am trying to validate a asset number (10 Characters) from one table
> to another table. Problem is they are in two different DB's and
> haven't done that before?
>
> Basically trying to take record 1 from Table 1/DB1 and validate it
> against record 1 in Table 2/DB2 if the record doesn't exist in Table 2/
> DB2 update the flag to N  in Table 1/DB1..... Seems easy but been
> working on it for about a week now no luck.... :-( I have the mostly
> the same colums in both tables but Table 2/DB2  is the host table I
> need to verify from....
>
> Does anyone have any help or code to help me do this??
>
> Thanks

See:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/dblink/doc/query?rev=26230



Re: SQL Query Validate Records Multiple Tables - HelpNeeded

From
"Bart Degryse"
Date:
I'm working on something similar at the moment. Don't know about you, but I have an additional problem: I have to deal with rather large datasets (>20000 recs per query) to be moved between the two databases (Oracle and PostgreSQL in my case). After doing lots of performance test, for me the conclusion was that dblink doesn't really work, dbi-link works but has way too much overhead and is way to slow.
So finally I installed plperlu and wrote some functions of my own and these work at a very acceptable speed. Transferring data between two different Oracle databases was on average not faster than between Oracle and PostgreSQL.

>>> Rodrigo De León <rdeleonp@gmail.com> 2007-05-16 0:32 >>>
On May 15, 8:17 am, Paul251 <savethe...@gmail.com> wrote:
> Hello...
>
> I am trying to validate a asset number (10 Characters) from one table
> to another table. Problem is they are in two different DB's and
> haven't done that before?
>
> Basically trying to take record 1 from Table 1/DB1 and validate it
> against record 1 in Table 2/DB2 if the record doesn't exist in Table 2/
> DB2 update the flag to N  in Table 1/DB1..... Seems easy but been
> working on it for about a week now no luck.... :-( I have the mostly
> the same colums in both tables but Table 2/DB2  is the host table I
> need to verify from....
>
> Does anyone have any help or code to help me do this??
>
> Thanks

See:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/dblink/doc/query?rev=26230


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match