[AccessD] updating records in SQL Server with a clustered index

All of the work I do uses dynamic SQL and I am moving away from stored procedures.

John W. Colby
www.ColbyConsulting.com

On 9/17/2010 2:59 PM, Steve Goodhall wrote:
>    BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px;
> }Have you considered using a stored procedure?
>   Regards,
>   Steve Goodhall, MSCS, PMP
>   248-505-5204
>   On Fri 17/09/10  2:17 PM , jwcolby jwcolby at colbyconsulting.com sent:
>   I am performing a "big picture" operation where records in an
> "address" table are copied to an "old
>   address" table when an address changes.  The table has a clustered
> index on it, using the PK as the
>   key for the index.
>   I had intended to do a copy, delete and append.  IOW delete the old
> address records to "get them out
>   of the way" and then append the new records back in in the "holes
> created".
>   Obviously this is the brute force way, and simply updating every
> field except the PK makes more
>   sense.  this makes more sense due to how the clustered index works
> and all the work that has to be
>   done to delete a record "in the middle" of a table with a clustered
> index.
>   So the delete / add operation becomes an update operation.
>   The update syntax gets ugly only because the field names are
> identical and the table comes from a
>   temp database, joined to the data in the current database.
>   Uggg.
>   I don't want to used a stored view because this has to happen in a
> dynamic process on any selected
>   database.
>   Any thoughts on how to go about this?
>   --
>   John W. Colby
>   www.ColbyConsulting.com
>   --
>   AccessD mailing list
>   AccessD at databaseadvisors.com [2]
>   http://databaseadvisors.com/mailman/listinfo/accessd
>   Website: http://www.databaseadvisors.com
>
>
> Links:
> ------
> [1] http://www.ColbyConsulting.com
> [2] mailto:AccessD at databaseadvisors.com