Query of queries: insert into another datasource

[Arielladog]

Hey guys,

Essentially I need to get some info from one datasource and then insert that
data into another datasource. Rather than using cfloop to do this, I was
hoping maybe I could accomplish something like this with a query of queries.
However, I don't know how to specify that I want to use both a dbtype="query"
and a datasource for the cfquery (or atleast when I do, it doesn't work).

Essentially I wanna do something like:

<cfquery datasource="datasource1" name="resultSet">
(SELECT '#mnemonic#' AS clientID, exch, currency FROM cld WHERE class=1 AND
clientType=#allLimits["cash"]#)
</cfquery>

<cfquery datasource="datasource2" dbtype="query">
INSERT INTO someTable VALUES (SELECT * FROM resultSet)
</cfquery>

Thanks,
aDog

[mxstu]

I don't believe that you can do this. I think dbtype="query" and
datasource="..." are mutually exclusive.

Most DBMS provide the capability of querying two different databases, on the
same server, in one SQL statement (or querying between linked tables or
databases). What type of database are you using and are the databases on the
same server?

[Arielladog]

Hey mxstu,

The database server is Sybase, but regretfully, they're on different servers.
It looks like I'll just settle to looping through and getting the results.

Usually using a query of queries makes sense because it uses cached info
rather than hitting hte dbserver again. In this case, there's not really an
advantage, except it may be quicker than looping through and obviously it's
easier for the developer.

aDog

[mxstu]

Doesn't Sybase have the abilty to link to another server? If so, you could
create a link to the source server on the destination server and then you
should be able to run a query directly between the two. The idea is ...

<cfquery datasource="server2Datasource" ....>
INSERT INTO server2.someDB.owner.someTable (someColumn)
SELECT someColumn
FROM server1.someDB.owner.someTable
WHERE ... some conditions....
</cfquery>

I'm not 100% certain, but I think sybase may have this capability.

[mxstu]

Doesn't Sybase have the abilty to link to another server? If so, you could
create a link to the source server on the destination server and then you
should be able to run a query directly between the two. The idea is ...

<cfquery datasource="server2Datasource" ....>
INSERT INTO server2.someDB.owner.someTable (someColumn)
SELECT someColumn
FROM server1.someDB.owner.someTable
WHERE ... some conditions....
</cfquery>

I'm not 100% certain, but I think sybase may have this capability.

[mxstu]

Doesn't Sybase have the abilty to link to another server? If so, you could
create a link to the source server on the destination server and then you
should be able to run a query directly between the two. The idea is ...

<cfquery datasource="server2Datasource" ....>
INSERT INTO server2.someDB.owner.someTable (someColumn)
SELECT someColumn
FROM server1.someDB.owner.someTable
WHERE ... some conditions....
</cfquery>

I'm not 100% certain, but I think sybase may have this capability.