Could this be combined with an ELSE statement in SQL? That is, insert if
new, update if existing? Thanks, Che
-Original Message-
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.
cfquery datasource=#variables.dsn#
Yes.
if exists(statement)
something
else
something else
The question I have to look up is if the whole operation is one 'unit' or
does it have to have transaction control. Is there a 'gap' between the
exists() check and the actual sql operation?
On Fri, Jan 28, 2011 at 9:15 AM, Che
, 2011 10:58 AM
To: cf-talk
Subject: Re: Insert without checking existance
Yes.
if exists(statement)
something
else
something else
The question I have to look up is if the whole operation is one 'unit' or
does it have to have transaction control. Is there a 'gap' between the
exists() check
-Original Message-
From: Michael Dinowitz [mailto:mdino...@houseoffusion.com]
Sent: Friday, January 28, 2011 10:58 AM
To: cf-talk
Subject: Re: Insert without checking existance
Yes.
if exists(statement)
something
else
something else
The question I have to look up
Is exists() more efficient than this?
Yes. I use it all of the time. Exists() will look for until it finds a record
and as soon as it finds it, it stops because the criteria is met. A normal
Select statement will look at all records and finish once all records have been
parsed. It's really
Dinowitz [mailto:mdino...@houseoffusion.com]
Sent: Friday, January 28, 2011 11:13 AM
To: cf-talk
Subject: Re: Insert without checking existance
I was doing the @@rowcount version to check then insert but I was
unhappy
with the performance. I was looking for some something a little better.
Still
Yes, there is a small gap between existence checking and the actual insert.
Unless you specifically commit the transaction early, all the statements
would be in the same transaction. That is true with all the possible
methods. Transactions are not the issue. Locking is, but it is only an issue
So just out of curiosity, is this on a SQL 2008 box? If so then you can
use merge and give different options for insert or update (even delete
if you want)
Steve
~|
Order the Adobe Coldfusion Anthology now!
MSSQL 2000.
While this is for a client, a lot of what I'm doing here is going to applied
to my own stuff. The client will be moving to MSSQL 2008 soon but I'm
assessing if I can stay on MSSQL 2000, can move to MSSQL 2008, or should
just move over to MySQL for my own stuff.
The licensing on MSSQL
...@houseoffusion.com]
Sent: Friday, January 28, 2011 2:13 PM
To: cf-talk
Subject: Re: Insert without checking existance
MSSQL 2000.
While this is for a client, a lot of what I'm doing here is going to
applied
to my own stuff. The client will be moving to MSSQL 2008 soon but I'm
assessing if I can
I generally prefer validation rather than an expectation of exceptions. What
about using the EXISTS?
I think this should work
if EXISTS (select keyword from table where keyword = '#keyword#')
-- do nothing
ELSE
-- do insert
On Thu, Jan 27, 2011 at 6:06 PM, Michael Dinowitz
Is exists() more efficient than this?
SET NOCOUNT ON
Select keywordid
from keywords
where keyword =@keyword
if @@ROWCOUNT 1
begin
Insert into keywords(keyword)
values(@keyword)
end
SET NOCOUNT OFF
I'm using a stored procedure for the tiny performance savings I may get.
On Thu, Jan 27,
Is exists() more efficient than this?
That I don't know. I suspect it is, and that it's pretty nominal.
~|
Order the Adobe Coldfusion Anthology now!
There are a number of ways to solve this issue. The solution Michael Grant
recommended is a good one and is probably the most common approach. The test
for existence should be slightly faster than getting the exact row count,
although the speed difference is likely unnoticeable.
Assumption:
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
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 =
16 matches
Mail list logo
