?
Solved

Insert cfquery output into another Datasource table

Posted on 2007-09-29
7
Medium Priority
?
739 Views
Last Modified: 2013-12-24
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>


0
Comment
Question by:zlinst28
  • 5
  • 2
7 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 19985419
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.

0
 

Author Comment

by:zlinst28
ID: 19985441

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!!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 19985462
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
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 

Author Comment

by:zlinst28
ID: 19985477
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
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 19985496
Well for a one-time import you could use sql server's import wizard (assuming you have access). If you want to do it using CF only, a simple option is to loop through the query and insert the data, one row at a time.  While this technique would not be desirable for very large or recurring inserts, it should do the job here.  

I don't know the data types of your column types, so I left that part out.  Feel free to ask if you have questions.


<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="HR_Repo" datasource="HR_Repository">
    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">
    )
    FROM denorm
   </cfquery>
</cfloop>

There are other options as well, like linkedservers, OPENQUERY(), OPENDATASOURCE(), etc. but that's probably overkill for a one-time import.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 19985501
>  <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....
0
 
LVL 52

Expert Comment

by:_agx_
ID: 19986203
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>
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question