Mark Breen
marklbreen at gmail.com
Sun Sep 19 09:49:56 CDT 2010
Hello John, Not really sure that I understand from the email what you are trying to do, and not sure what you are asking advice about? What I am assuming your asking is how best to do the following 1) You have an existing address with PK 2) you have in a temp table a new address 3) you want to take the existing address, and save it in a address archive table 4) You want to overwrite the existing address with the new details 5) you want to move to the next record in the temp table ? Is that right? What do we need to consider relating to the clustered index (as you mentioned it, I guess there must be something to consider)? on about ten occasions in the last ten years, I have resorted to cursors. I found them easy to work with, and especially when I wanted to track my steps and perform my work in a sequential fashion. When I used cursors speed of updates went from 1-2 seconds to minutes sometimes, but on the other hand, by using cursors, I was able to handcraft the exact sql I wanted applied, and I could monitor progress. I could even work in batchs eg 1000 records and stop. Have you ever played with them? I think with your enormous volumes of data, you might be able to make use of cursors IOW, open a cursors for the addresses that are in the temp table, and then you can neatly apply updates and archive statements to the main db tables. Of course if there is 52 million records in the temp table then forget this approach :) Mark On 17 September 2010 19:17, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >