Bob/Rob, Here's the link for the syntax from mySQL for the INSERT INTO .... SELECT statement:
http://dev.mysql.com/doc/refman/5.1/en/insert-select.html Dave -----Original Message----- From: Rob Parkhill [mailto:[email protected]] Sent: Thursday, April 23, 2009 9:17 AM To: cf-newbie Subject: Re: insert into select - mysql Bob, not sure why you have the select? Typically an insert is paired with the VALUES Statement. If you have the values that you want to insert in a separate table, you will need a query to get the values out first <cfquery name="tempVotes" datasource="packets"> Select vote_attempted,ipaddress,name,packet_id,reason,vote,voter_id from temp_votes</cfquery> <cfquery name="commitVotes" datasource="packets"> INSERT INTO packet_votes(date_voted,ipaddress,name,packet_id,reason,vote,voter_id) VALUES (tempVotes.vote_attempted,tempVotes.ipaddress,tempVotes.name,tempVotes.packe t_id,tempVotes.reason,tempVotes.vote,tempVotes.voter_id) </cfquery> Then you can loop over the insert if you needed or whatever. The insert really is a two stage process in this instance. Of course if the values you were inserting were in a form, then you wouldn't need the original query and would replace tempVotes with form in the insert query. HTH Rob On Thu, Apr 23, 2009 at 9:51 AM, Imperial, Robert <[email protected]>wrote: > > Pardon the duplicate unfinished post :) fat fingers this morning. > > > > -----Original Message----- > From: Bob Imperial [mailto:[email protected]] > Sent: Thursday, April 23, 2009 10:28 AM > To: cf-newbie > Subject: insert into select - mysql > > > Hi folks, > > I'm trying to do something that seems like it should be relatively > simple but have yet to get it right. In MySQL I have 2 tables, > temp_votes and packet_votes. The temp_votes table holds all voting data > until all packets have been voted on. I am trying to figure out the > syntax for doing INSERT INTO SELECT but have yet to get it right. > My last attempt looks like this: > > <cfquery name="commitVotes" datasource="packets"> > INSERT INTO > packet_votes(date_voted,ipaddress,name,packet_id,reason,vote,voter_id) > SELECT (vote_attempted,ipaddress,name,packet_id,reason,vote,voter_id) > FROM temp_votes > </cfquery> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4535 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
