A few T-SQL questions...

I have a few T-SQL questions that i'm hoping someone can answer for me.

1) Is there an easy way to do an "insert if no record exists, otherwise
update"? What I do now is query for the record and check recordcount, if
it exists I execute an update, otherwise insert, but this requires 2 round
trips (i'm not using a stored procedure for this for several reasons).

2) What's the best way to write an update statement so that only fields
that have changed are updated? I have a table that has a trigger that does
certain things when certain fields change. I don't want to update the
field with the current value or the trigger will process those values (even
though they didn't change). Additionally, I can't use null fields to
indicate no change because I may have to set the values to null.

I can really only think of two ways to handle this. a) build my sql
dynamically for what I need to update at run-time or b) Create a whole
bunch of specialized stored procedures to update varying permutations of
the data.

I don't like either solution. Is there some better solution here that i'm
missing? I'm hoping there is a technique I can use to effectively cancel
out the update of specific fields based on some criteria (other than a
monster if statement).

Thanks.
.

Relevant Pages

  • RE: Display data updated in a trigger after SqlDataAdapter.Update
    ... The SqlCommandText is an Update statement. ... "John Papa" wrote: ... > an Output parameter from the stored procedure to the ADO.NET code. ... in the database I have a trigger which updates ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How can a recordset returned by a sproc be updated?
    ... I have just finished simple test on pubs database and SQL ... Server Profiler shows nothing about an UPDATE statement. ... > because I only gave my application knowledge of the stored procedure, ... >>Perhaps you have a trigger defined on the table. ...
    (microsoft.public.sqlserver.programming)
  • Cursor already exists conflict
    ... The problem is caused by an incompatibility between my update trigger ... and the stored procedure that issues the update statement. ... I have a necessary cursor to loop through records. ...
    (microsoft.public.sqlserver.programming)
  • Re: chooses not to generate code at all
    ... >>>DBMS is so that the DBMS engine can execute it when triggered by some ... If there is no trigger, ... Stored procedure is one thing. ... > If the procedure execution is not triggered by DBMS ...
    (comp.object)
  • Re: Insert Error 30014
    ... of @@identity before the stored procedure finish: ... the trigger. ... I have a form where I want to have underlying record source of a single ... EventId ...
    (microsoft.public.access.adp.sqlserver)
Loading