query1 from db1 and update db2 from the values extracted from query1

[shakiracarey]

HI,

i have this html query report . works great
it queries from the db1 and outputs in html format.
my next assignment is to update a database db2 by inserting values from the query results i got from db1

how do i do this??

thanks in advance.

[Dan_Bracuk]

The best approach is to not do it at all, just use the data from db1.

If that is not an option, see if there is a way to do it outside cold fusion.

If you can't do that, see if you can link the db's somehow, then it's a single query.

The last resort would be,
<cfoutput query = "yourquery">
<cfquery>
insert, ...
closing tags.

For the record, I did one of those last resort things today.

[shakiracarey]

why did i you write "best approach is to not do it at all"

what are the short comings of writing a application like this?

also

the query results extracted from db1 (database 1 (oracle9)) is still needed to update the db2 (database 2 (oracle9))

is there another way of building an application???

[Dan_Bracuk]

quote:

---

Originally posted by: shakiracarey
why did i you write "best approach is to not do it at all"

---

because you already have the data.

[shakiracarey]

the data is from a query result to html output.

you mentioned" see if there is a way to do it outside cold fusion."
please explain?

also, what is a good systematic apporach for this problem?
thanks

[JMGibson3]

All in one step (assuming same schema, you'll need to supply qualifiers and/or may have to the DBA's involved if different schema or instances). Even more efficiently you should write a Stored Procedure right in Oracle and call it from CF. At any rate syntax on either side would be:

Insert INTO tblForDB2
Select whatever AS col1nameinDB2, whatever AS col2nameinDB2, etc. FROM tblForDB1

Alternatively but much slower
<cfloop query="db1Query">
INSERT into tblForDB2 (col1nameDB2, col2nameDB2, etc)
VALUES #whateverfromDB1#, #whateverfromDB1#, etc. with or without quotes as needed.
</cfloop>

[shakiracarey]

it is different dataabse