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>
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>
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.OtherTa ble(....Co lumns...)
SELECT ... Columns...
FROM firstdatabase.owner.Source Table
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.OtherTa
SELECT ... Columns...
FROM firstdatabase.owner.Source
ASKER
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
Thanks...
zack
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> <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="YourOtherDatas ource">
INSERT INTO TheOtherTable (Site_Name, Site_ID, Site_Type, Manned_Unmanned)
... etc....
Correction. The inner query should use a separate name and obviously the second datasource name
<cfquery name="TransferDataRow" datasource="YourOtherDatas
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="YourOtherDatas ource">
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>
<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="YourOtherDatas
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>
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.