I'm a beginner, and I need major help...
I'm attempting to devise a coldfusion page that will first query two tables (containing customer info) and compare the records in one table to the other to find any customer numbers that may be present in one table but not in the other.
Some background: one of these tables I'm importing nightly (from a propriety database that does not handle sql queries very well) into a Microsoft SQL database. That table is called ar1.
I then have created my own table in the same Microsoft SQL database called billing_comments. This table does not update itself. If a new customer is added to table ar1 (when the nightly import occurs) then I need to tell my table, billing_comments, to add a record for this new customer that is found in ar1.
Here's my code so far along with some notes:
<cfquery name="newCust" datasource="TestMas90">
SELECT CustomerNumber
FROM ar1
WHERE (CustomerNumber NOT IN
(SELECT customernumber
FROM billing_comments));
</cfquery>
<html>
<head>
<title>Aged Invoice Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfoutput query="newCust">
#newCust.CustomerNumber#
*****************************************************************************************************************************
THIS IS WHERE I NEED AN INSERT STATEMENT. I NEED AN INSERT RECORD FOR EVERY #NEW.CUSTOMERNUMBER# THAT IS FOUND AND PRINTED.
BUT, THE CODE I INTEND TO USE WHICH IS BELOW, CANNOT BE PLACED BETWEEN MY <CFOUTPUT QUERY></CFOUTPUT> TAGS.
THE CODE I WISH TO USE:
-----------------------------------------------------------------
<cfquery name="insertNewCust" datasource="TestMas90">
INSERT INTO billing_comments (CustomerNumber, BillCom, Reason)
Values('#variables.custNum#','n/a','n/a')
</cfquery>
-----------------------------------------------------------------
*****************************************************************************************************************************
</cfoutput>
</body>
</html>
I'm attempting to devise a coldfusion page that will first query two tables (containing customer info) and compare the records in one table to the other to find any customer numbers that may be present in one table but not in the other.
Some background: one of these tables I'm importing nightly (from a propriety database that does not handle sql queries very well) into a Microsoft SQL database. That table is called ar1.
I then have created my own table in the same Microsoft SQL database called billing_comments. This table does not update itself. If a new customer is added to table ar1 (when the nightly import occurs) then I need to tell my table, billing_comments, to add a record for this new customer that is found in ar1.
Here's my code so far along with some notes:
<cfquery name="newCust" datasource="TestMas90">
SELECT CustomerNumber
FROM ar1
WHERE (CustomerNumber NOT IN
(SELECT customernumber
FROM billing_comments));
</cfquery>
<html>
<head>
<title>Aged Invoice Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfoutput query="newCust">
#newCust.CustomerNumber#
*****************************************************************************************************************************
THIS IS WHERE I NEED AN INSERT STATEMENT. I NEED AN INSERT RECORD FOR EVERY #NEW.CUSTOMERNUMBER# THAT IS FOUND AND PRINTED.
BUT, THE CODE I INTEND TO USE WHICH IS BELOW, CANNOT BE PLACED BETWEEN MY <CFOUTPUT QUERY></CFOUTPUT> TAGS.
THE CODE I WISH TO USE:
-----------------------------------------------------------------
<cfquery name="insertNewCust" datasource="TestMas90">
INSERT INTO billing_comments (CustomerNumber, BillCom, Reason)
Values('#variables.custNum#','n/a','n/a')
</cfquery>
-----------------------------------------------------------------
*****************************************************************************************************************************
</cfoutput>
</body>
</html>