Hi all,
I'm trying to get the following to work.  
1) Query an existing access db table, loop the results (in this case, about
3000 records), and insert each record sequentially into the appropriate SQL
2000 table.
2) Problem is, should I be using a query loop, or an index loop to insert
the records (the new SQL table creates the unique increment value).  The way
I have the code now throws an error with the "for" statement.
======================

<!--- 1. Retrieve records from the Access db --->
<cfquery name="getRecords" datasource="powellEmail">
SELECT *
FROM pm
ORDER BY emailaddress
</cfquery>

<!--- 2. Insert the records into the SQL database --->
<!--- Loop through the getRecords query fields and update the new table.
--->

<cfloop from="1" to="#getRecords.recordcount#" index="ThisRow">

<cfset  VARIABLES.confirmationID = #CreateUUID()#><!--- Create a Unique
Identifier to use as a confirming Email ID Number --->

<cfquery name="INSObj1" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
INSERT INTO #REQUEST.prefix#_Email_AddressList1
(firstname,
lastname,
emconfID,
emailaddress)
VALUES (<cfif Len(getRecords.firstName)><cfqueryparam
cfsqltype="cf_sql_varchar" value="#Evaluate("getRecords.firstName" &
ThisRow)#">, </cfif>
<cfif Len(getRecords.lastName)><cfqueryparam
cfsqltype="cf_sql_varchar" value="#Evaluate("getRecords.lastName" &
ThisRow)#">, </cfif>
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#Evaluate("VARIABLES.confirmationID" & ThisRow)#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#Evaluate("LCase(getRecords.emailaddress)" & ThisRow)#">
</cfquery>

<!--- Get the last inserted record to pass through to the email sendout
routine ////////////////////////  --->
<cfquery name="getLastRecord" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
SELECT
MAX(#REQUEST.prefix#_Email_AddressList1.emailID) as emailID
FROM #REQUEST.prefix#_Email_AddressList1
</cfquery>


<!--- Associate the subscriber with the groups --->
<cfquery name = "INSOBJcat" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#">
INSERT INTO
#REQUEST.prefix#_Email_JoinAddressListGroups1 (subscriberGroupID, emailID)
VALUES (9, #getLastRecord.emailID#)
</cfquery>
</cfloop>

==================
Thanks, Mark
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

Reply via email to