|
|
I need some help with the code to import data from one table into another if it
is not a duplicate or if a record has changed.
I have 2 tables, Members and NetNews. I want to check NetNews and import
non-duplicate records from Members into NetNews and update an email address in
NetNews if it has changed in Members. I figured it could be as simple as
checking Members.MembersNumber and Members.Email against the existance of
NetNews.Email and Members.MemberNumber and if a record in NetNews does not
exist, create it and if the email address in Members.email has changed, update
it in NetNews.Email.
Here is what I have from all of the suggestions received from another category
last year. It is not complete, but I am stuck on the solution. Can someone
please help me get this code working? Thanks!
------------------
<cfquery datasource="#application.dsrepl#" name="qryMember">
SELECT distinct Email,FirstName,LastName,MemberNumber
FROM members
WHERE memberstanding <= 2 AND email IS NOT NULL AND email <> ' '
</cfquery>
<cfquery datasource="#application.ds#" name="newsMember">
SELECT distinct MemberNumber
FROM NetNews
</cfquery>
<cfif
not(listfindnocase(valuelist(newsMember.MemberNumber),qryMember.MemberNumber)
AND isnumeric(qryMember.MemberNumber))>
insert into NetNews (Email_address, First_Name, Last_Name, MemberNumber)
values ('#trim(qryMember.Email)#', '#trim(qryMember.FirstName)#',
'#trim(qryMember.LastName)#', '# trim(qryMember.MemberNumber)#')-
</cfif>
</cfloop>
</cfquery>
------------------
|
|