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

Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||It means in SQL Enterprise Manager, it only says the TempDB has 2 C-drive
files but not have the 2 D-drive files I added last night.
However, the 2 D-drive files still exist in the D drive when I used File
Explorer to search them.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:#uPqAqMREHA.2972@.TK2MSFTNGP09.phx.gbl...
> What does "cannot recognize" mean? Do you get an error message (as your
> subject implies)? If so, what is it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>|||Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly. Please recreate the files using using ALTER DATABASE command.
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezZkyaMREHA.3944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you execute the below statement in query analyzer:-
> sp_helpdb tempdb
> If it is not showing D drive file, then you have not added the new files
> properly.
> Refer ALTER DATABASE in books online to add a new file to D Drive. (You
can
> also use Enterprise manager)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>