ColdFusion 4.5 - How to loop thru query results and append to another table

I have a table 'A' in session.datasource1 and need to append the records to table 'A' in session.datasource2.
Table structures are identical.  I have query "getA1Recs".  How do I loop thru the query results from "getA1Recs" and append them to table 'A' in session.datasource2?

ptslv  
ptslvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kkhippleCommented:
what kind of datasource is it that you are storing your records in?
0
ptslvAuthor Commented:
both are MS Access databases.  The first is from data that is uploaded to the server.  The process is to upload the data, and append the records to the master database.
0
kkhippleCommented:
if you want to append this ot the backend db

then loop thru the query and with each iteraction insert into the table

<CFQUERY DATASOURCE="#SESSION.datasource1#" NAME="queryOne">
    SELECT col1, col2, col3
    FROM table1
</CFQUERY>

<CFLOOP QUERY="queryOne">

    <!--- insert data --->
   <CFQUERY DATASOURCE="#SESSION.datasource2#" NAME="queryTwo">
       INSERT INTO table2 ('#col1#', '#col2#', '#col3#')
   </CFQUERY>

</CFLOOP>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

ptslvAuthor Commented:
That works great.  Only problem I'm having is getting the Date field moved over.  I know it is a reserved word (yes, they used reserved words as field names), but I tried it using an alias and that didn't work.  Is there a way to pull in the Date field?  Also, I can't pull in DateStamp_Submit field.  I tried using '#evaluate(#DateStamp_Submit#)#' but got an error on that, too.
0
kkhippleCommented:
even tho using reserved words as field names is very bad....

to make it work in your sql statemtn is to surrond the field name with [ ]  brackets

so something like this should work:


SELECT col1, col2, [date]
FROM table1
0
ptslvAuthor Commented:
I did that.  That didn't work either
0
kkhippleCommented:
could you paste your code and the error pls
0
ptslvAuthor Commented:
The 'Date' field type is Date/Time with default in the database of Now()

Code:
<cfquery name="getdataA" datasource="#session.datasource2#">
      Select AutoNumID, DATESTAMP_Submit, Test, [Date] as PDate
FROM tbl_A
</cfquery>

<cfloop query="getDDPers">
      
      <cfquery name="insertdata" datasource="#session.datasource1#">
            Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
            Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', '#PDate#')
            
            </cfquery>
      </cfloop>


Error:

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:3) to (18:64).
0
kkhippleCommented:
try taking away the column names like so....

<cfloop query="getDDPers">
     
     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', '#PDate#')
         
          </cfquery>
     </cfloop>
0
ptslvAuthor Commented:
I've gone thru removing all the fields and adding them one at a time.  The only field that throws the error is the Date field;

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The DateStamp_Submit field was the same until I changed it by adding '_Submit' to the end of the name.  But I can't change the field names in the databases, so I am trying to use the alias.

If you can help me figure it out, I will add 50 points.  :-)
0
ptslvAuthor Commented:
when I changed '#PDate#' to '#evaluate(PDate)#', I got this error:

An error occurred while evaluating the expression:
#evaluate(PDate)#
Error near line 20, column 26.
--------------------------------------------------------------------------------

An error has occurred while processing the expression:

   2004-11-09 08:44:12

Invalid parser construct found on line 1 at position 12. ColdFusion was looking at the following text:

08
Invalid expression format. The usual cause is an error in the expression structure.

The error occurred while processing an element with a general identifier of (#evaluate(PDate)#), occupying document position (20:25) to (20:41).

0
kkhippleCommented:
oh okay... one thing you can do first is to dump the contents of your query...

<CFOUTPUT>
   <CFDUMP var = #getDDPers#>
</CFOUTPUT>

this will allow you to see what you are getting...

another thing.. if the date column is a date data type.... ie, it doesn't hold any other then dates... ie if you tried to enter 'some sort of text'  then it would give you an error

note:  in the cmd line for the db that you are using.. u can type in "DESC table_name"  to find out the structure

if thats' the case (ur Date table column is defined as a date date type) then when you are inserting the date then you might need to make sure its in the right format.. see if this owrks


<cfquery name="getdataA" datasource="#session.datasource2#">
     Select AutoNumID, DATESTAMP_Submit, Test, to_char ([Date], 'mm/DD/yyyy hh24:MI') as PDate
FROM tbl_A
</cfquery>

<cfloop query="getDDPers">
     
     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', to_date('#PDate#','YYYY-DD-MM HH24:MI:SS'))
         
          </cfquery>
     </cfloop>
0
ptslvAuthor Commented:
are to_char, to_date and CFDump in ColdFusion 5.0?  I'm using 4.5 and they give me errors.
0
Tacobell777Commented:
you would not use a cfloop!

You would use either

INSERT INTO tableName1
SELECT     *
FROM         tableName2

or you would do a select and then a Query of Query if the tbales were in different database

<cfquery name="tableName2".......>
SELECT *
FROM tableName1
</cfquery>

<cfquery dbtype="query" .....>
INSERT INTO tableName1
SELECT     *
FROM         tableName2
</cfquery>
0
Seth_BienekCommented:
Hi ptslv,

to_date is a server-side function, and may or may not work for you, depending on your database platform.

ditto for to_char.

cfdump and query-of-a-query will not work on CF 4.5.

Use CF's createodbcdatetime() function to insert a date into your table.  This will work across db platforms:

     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', #createodbcdatetime(PDate)#)          
        </cfquery>

Hope this helps.

Regards,

Seth Bienek
0
ptslvAuthor Commented:
Seth,

I tried #createodbcdatetime(PDate)#  and got the following error:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2004-11-09 08:44:12'}''.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:3) to (19:64).

My Code:

<cfquery name="insertPers" datasource="#session.datasource1#">
      Insert into tbl_Personnel(AutoNumID,DATESTAMP)
      Values (#AutoNumID#, '#createodbcdatetime(DATESTAMP)#')          
</cfquery>

ptslv
0
Seth_BienekCommented:
Hey ptslv,

You don't need quotes when using createodbcdatetime()..

Try this:
<cfquery name="insertPers" datasource="#session.datasource1#">
      Insert into tbl_Personnel(AutoNumID,DATESTAMP)
      Values (#AutoNumID#, #createodbcdatetime(DATESTAMP)#)          
</cfquery>

Regards,

Seth
0
Tacobell777Commented:
ptslv did you have a look at my suggestions, or was it a waste of my time?
0
DA_JEDCommented:
I think TacoBell and Seth are on the right track ptslv, let use know how it turns out

DAJED
0
ptslvAuthor Commented:
Tacobell,

I looked at your comments, but as I am developing with 4.5, I can't use the query of query.  And I am using 2 different database connections.  Thanks for the info, tho.  When I get a chance, I will test it off the actual server, which is running 5.0.  Go figure.

Seth,

I was still getting errors without the quotes.  So I rechecked the data and found it was dropping out when it hit empty data in the date field.  And since it is auto filled with Now() whenthe user inputs the data, I just put dates in the dummy data where there were none and ran the query without using 'createodbcdatetime()' and had no problems!

Thanks for all the help, tho.  I appreciate it!

ptslv
0
Tacobell777Commented:
all right, but we could have come up with another solution, looping and inserting is bad...

good luck..
0
ptslvAuthor Commented:
I agree.  But I'm in a time crunch to demo.  I'll have time to fine-tune.  Thanks again.
0
kkhippleCommented:
I'm glad I could help out.
And guys I know my programming skills are not at par with you guys.. but I'm laerning
and I'm on here to help as many ppl as I can

cheers,
KDK
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.