The same basic issue came up on the SQL list.
http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:898
While the question and result is based on a stored procedure, the same
code can go in a single cfquery statement. The operation is a simple
check for data existence and insert if it does not exist. All nice,
neat, and transaction controlled by SQL.
CREATE PROCEDURE getinsertnewssourceid
@newssource varchar(256), @newssourcehref varchar(256)
AS
Set NOCOUNT ON;
Select newssourceid
from newssources
where newssource = @newssource
and href = @newssourcehref;
if @@ROWCOUNT = 0
begin
Insert into newssources(newssource, href)
values(@newssource, @newssourcehref);
select newssourceid= scope_identity();
end
SET NOCOUNT OFF;
GO
--
Michael Dinowitz
On Wed, Apr 28, 2010 at 4:14 PM, nvc 1 <[email protected]> wrote:
>
> why can't you do this where mytable has an auto incrementing primary key
> called id:
>
> <cftransaction>
>
> <cfquery datasource="#dsn#" name="insertQuery">
> insert into mytable(acolumn)
> values(1)
> </cfquery>
>
> <cfquery datasource="#dsn#" name="getid">
> select max(id) as maxid from mytable
> </cfquery>
>
> </cftransaction>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333224
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
