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 <[email protected]
> 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
