This is what I'm using at the moment but I'm going to read what you have as well. I want fast and safe.
Thanks <cfquery datasource="#variables.dsn#"> if not exists( Select keywordid from keywords where keyword = <cfqueryparam value="#local.keyword#" cfsqltype="cf_sql_varchar">) Insert into keywords(keyword) values(<cfqueryparam value="#local.keyword#" cfsqltype="cf_sql_varchar">) </cfquery> On Thu, Jan 27, 2011 at 9:51 PM, Steve Bryant <st...@bryantwebconsulting.com > wrote: > > Michael, > > I typically use a "SELECT INTO" with a "NOT EXISTS" clause. That keeps the > entire thing in one tight query. > > > http://www.bryantwebconsulting.com/blog/index.cfm/2005/3/16/prevent_duplicate_inserts > > See also the link in the comments for a more fail-safe method (though > specific to SQL Server). > > Steve > > >I'm inserting a keyword into a table that only contains unique keywords > (db > >constraint). I'm currently doing a select to see if the keyword already > >exists and then an insert if it does not. This 2 step process is what I > >think of as 'correct'. First check, then act. > > > >After a little thought, the 'wrong' way might be better in this case. The > >wrong way is to just try the insert without first checking if the value > >already exists. If it fails, a try/catch deals with it. As messy as this > >sounds, it saves a full step and is probably the more efficient of the > two. > > > >Am I wrong to think that the second method is wrong? Should I do it this > >way? > > > >Thanks > > > >Michael Dinowitz > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341600 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm