Link to home
Start Free TrialLog in
Avatar of zlinst28
zlinst28

asked on

Insert cfquery output into another Datasource table

Hello,

I am atttempting to populate (INSERT INTO) a new datasource table from a CFOUTPUT SELECT STATEMENT(1000 records). Can anyone show me how this is done? Here is my initial query:

<cfquery name="HR_Repo" datasource="HR_Repository">
SELECT Site_Name, Site_ID, Site_Type, Manned_Unmanned
FROM denorm
</cfquery>


Avatar of _agx_
_agx_
Flag of United States of America image

1. What type of database are you using
2. Is the destination table in the same database?
3. Is this a one-time insert ?

I ask because there are several options for doing this type of insert, but the best method depends on the answers to those questions.

Avatar of zlinst28
zlinst28

ASKER


We are using a SQL SERVER 2005

The exporting table is in a different DSN than the one I am trying to import into.

This is just a one time push from the originating table into an empty table with the same field names.

Thanks for the help!!
You're welcome.  

One more question, I take it the the target table is on a different sql server?  Otherwise, you just could just
the 3/4-part naming syntax like this  (assuming you had the correct permissions).

INSERT INTO seconddabase.owner.OtherTable(....Columns...)
SELECT  ... Columns...
FROM     firstdatabase.owner.SourceTable
Actually the database I am running my CFQUERY SELECT is in Access and the INSERT will be into a SQL 2005, sorry to leave that part out!

Thanks...

zack
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>  <cfquery name="HR_Repo" datasource="HR_Repository">

Correction.  The inner query should use a separate name and obviously the second datasource name

    <cfquery name="TransferDataRow" datasource="YourOtherDatasource">
       INSERT INTO TheOtherTable (Site_Name, Site_ID, Site_Type, Manned_Unmanned)
      ... etc....
For PAQ purposes, the whole thing would be something like this

<cfquery name="HR_Repo" datasource="HR_Repository">
SELECT Site_Name, Site_ID, Site_Type, Manned_Unmanned
FROM denorm
</cfquery>

<cfloop query="HR_Repo">
   <cfquery name="TransferDataRow" datasource="YourOtherDatasource">
    INSERT INTO TheOtherTable (Site_Name, Site_ID, Site_Type, Manned_Unmanned)
    VALUES
    (
       <cfqueryparam value="#Site_name#" cfsqltype="The column data type here">,
       <cfqueryparam value="#Site_ID#" cfsqltype="The column data type here">,
       <cfqueryparam value="#Site_Type#" cfsqltype="The column data type here">,
       <cfqueryparam value="#Manned_Unmanned#" cfsqltype="The column data type here">
    )
   </cfquery>
</cfloop>