many thanks
With your help I have managed to get a bulk update/insert going
However I cannot work out how to account for items originally on database
but not on new group list
ie groups that need to be deleted from original database after update
process (is that understandable?)
Any help gratefully received
Seamus
Code here:
<!--- Read text file and set variable name --->
<CFFILE action="READ" file="C:\InetPub\wwwroot\abc\Database\group.txt"
variable="grouptext">
<!--- Get total list length --->
<cfset lstGroupLen = ListLen(groupText,"|")>
<!--- Loop thru list stepping by 2 as 2 pipes per line --->
<cfloop from="1" to="#lstGroupLen#" step="2" index="idx">
<cfset grpDescIdx= idx + 1>
<cfif grpDescIdx LTE lstGroupLen>
<cfoutput>
<!--- Check if already in database --->
<cfquery name="checkgroup" datasource="dsn">
SELECT *
FROM tblGroupCopy
WHERE GroupCode='#Trim(ListGetAt(groupText,idx,"|"))#'
</cfquery>
<!--- Check if exists - if so - update - else add --->
<CFIF checkgroup.RECORDCOUNT GT 0>
<cfquery name="qUpdate" datasource="dsn">
UPDATE tblGroupCopy
SET
GroupDesc='#Trim(ListGetAt(groupText,grpDescIdx,"|"))#'
WHERE
GroupCode='#Trim(ListGetAt(groupText,idx,"|"))#'
</CFQUERY>
<CFELSE>
<cfquery name="qInsert" datasource="dsn">
INSERT INTO
tblGroupCopy(GroupCode,GroupDesc)
VALUES (
'#Trim(ListGetAt(groupText,idx,"|")
'#Trim(ListGetAt(groupText,idx,"|"))#',
'#Trim(ListGetAt(groupText,grpDescIdx,"|"))#'
)
</CFQUERY>
</CFIF>
</cfoutput>
</cfif>
</cfloop>
At 10:21 pm 5/06/01 , you wrote:
>You can also use the pipe as the delimiter. This code uses that and steps
>over every other list element to get the group code and does the same to get
>the description. The cfif just makes sure you don't have a stray | at the
>end of the list.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
