Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Direct insert wtihin database tables..

Status
Not open for further replies.
Extras

Extras

Technical User
Joined
Nov 16, 2001
Messages
232
Location
US
I am trying to do the following:

<CFQUERY NAME=&quot;Insert&quot; DATASOURCE=&quot;MyDSN&quot;>
INSERT INTO Mail_List (ML_FirstName, ML_LastName, ML_Email, ML_Type )
VALUES Customers('FirstName', 'LastName', 'EMail', 'Customer')
WHERE ((customers.EmailOK)=True)
</CFQUERY>

<body>
We have added the customers info into the mailing list.
</body>

As you can see I am trying to move data from the customers table (based on the value of EmailOK) into the mailing list table directly.

I am using an Access database with text fields and I get the following error:
Syntax error in INSERT INTO statement.

Any help would be appreciated!
 
Sort by date Sort by votes
Actually I did come up with an answer - but would be greatful to know if someone has a more elegant solution..

I split up the code into a SELECT and then an INSERT query and then enclosed it within a LOOP based on the EmailOK variable.

I am also thinking of what I would have done if I did not have the EmailOK condition and wanted to just go ahead and insert all the instances from the customer table into the mail_list table? How would then I base the loop for this process? Could I base it on the a recordcount on the number of customers? I know this is a slightly different question but any direction would be appreciated.

Anyway here is the code...


<cfquery name=&quot;Select&quot; datasource=&quot;MYDSN#&quot;>
SELECT FirstName, LastName, EMail
FROM Customers
WHERE ((customers.EmailOK)=True)
</cfquery>

<cfloop query=&quot;Select&quot;>
<CFQUERY NAME=&quot;Insert&quot; datasource=&quot;MYDSN#&quot;>
INSERT INTO Mail_List (ML_FirstName, ML_LastName, ML_Email, ML_Type )
VALUES ('#FirstName#', '#LastName#', '#EMail#', 'Customer')
</CFQUERY>
</cfloop>

<body>
We have added customers info to the mailing list!
</body>
 
Upvote 0 Downvote
What you want, I think, is this:

<CFQUERY NAME=&quot;Insert&quot; datasource=&quot;#MYDSN#&quot;>
INSERT INTO Mail_List (ML_FirstName, ML_LastName, ML_Email, ML_Type )
SELECT #FirstName#', '#LastName#', '#EMail#', 'Customer')
FROM Customers
WHERE EmailOK = true
</CFQUERY>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lollieleaver
  • Locked
  • Question Question
Dreamweaver CS4 - how can I make an Access database downloadable from my site?
Replies
2
Views
331
lollieleaver
lollieleaver
RosieGp
  • Locked
  • Question Question
Insert and update/append in coldfusion
Replies
5
Views
606
philhege
philhege
Floyd7
  • Locked
  • Question Question
Import XML Feed into Database
Replies
1
Views
284
philhege
philhege
swansong77
  • Locked
  • Question Question
Output query and alert if there is a duplicate between 2 tables
Replies
3
Views
370
r937
r937
Pugman
  • Locked
  • Question Question
SQL query on 3 tables works, 5 tables doesn't work
Replies
8
Views
477
Pugman
Pugman

Part and Inventory Search

Sponsor

Back
Top