Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alun02
Starting Member
5 Posts |
 Posted - 2007-07-11 : 10:44:22
|
| Hi guys,I need to write a script which calculates a set of new rows for a table (takes about 10 mins) and then swaps these new values into a table, removing all the old ones which can then be discarded. The way I'm thinking of doing this is to create a temporary table, populate it with the new values then once that's done removing all entries from the old table and inserting the new ones in. The purpose of the exercise is to minimise the amount of time there is no data in the table, so that queries on the table return successfully as much as possible. Is the temporary table idea the best or is there an alternative mechanism I can use?Thanks in advance,Alun |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-11 : 10:52:52
|
| put the rows in the temp table, do the processing.then just update the values, don't delete and insert themthis way you'll have one operation instead of two._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
alun02
Starting Member
5 Posts |
Posted - 2007-07-11 : 10:55:46
|
| Hi spirit1,That would work in most cases, but it may be that some of the rows in the original table may need to be deleted in the update, i.e. there may be fewer rows in the table after the 'swap' than there were before. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-11 : 10:59:41
|
| well then... i think your way will do just fine._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
alun02
Starting Member
5 Posts |
Posted - 2007-07-11 : 11:47:26
|
| Just to let anyone that's interested know, the method I ended up using was to create a new table, populate it with the new values then once that was done renaming the old table, renaming the new one to be the same as the original one and dropping the old table. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-07-12 : 11:10:36
|
| be aware....when you drop a table...you drop the permissions that go with it...in this instance this technique may not be causing you a problem...but other times it may be, depending on the scurity model you are using....(and I hope you're not using the "sa" userid!!) |
|
|
|
|
|
|
|
|
Swapping tables