having a constraint on a adding a record

how can i make a stored procedure for inserting a record in the database where it detect if the title is already present and then disregard insertion and just update the number of copies in that specific record?

i would be so thankful for any hel out there...tnx!

insert into yourtable(pk, col1, col2, col3)
select @.pk, @.col1, @.col2, @.col3
where not exists (select * from yourtable x where x.pk = @.pk)

if @.@.rowcount = 0 -- nothing inserted
begin
-- so do the update
update yourtable
set col1 = @.col1,
col2 = @.col2,
col3 = @.col3
where pk = @.pk
end|||

It would help to post the schema of the table or relevant columns. You can do something like below:

begin tran

if not exists( select * from titles with(updlock) where title = @.title )

insert into titles

values(....)

else

update titles

set numcopies = numcopies + 1

where title = @.title

commit

You need to add error handling and other necessary checks to the code.

|||Arguably the best way to do this would be using an isntead of INSERT trigger. In the trigger, run the if exists statement in the above posts, and if so, update, otherwise insert. That way you can rely on the code running when anything is inserted without having to update all your stored procedures to make that change.|||Sure, this is one way to do it. But you will have to watch out for performance issues. INSTEAD OF trigger requires materialization of the rows in the inserted/deleted tables. And this can be expensive depending on the number of rows being inserted. And for single row inserts the overhead of the trigger is probably unnecessary and it is easier to modify the SP that performs the insert into the base table.sql