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 |
|
hasanali00
Posting Yak Master
207 Posts |
 Posted - 2006-03-17 : 05:16:26
|
| Let me pick your brain on this.I have 2 tables: Table_1 (productID) and Table_2 (productID)Table_1 needs to contain exactly the same data as Table_2.I believe, I have 2 options:1 - Truncate Table_1 ...and then Insert into table Table_1 from Table_22a - Delete from Table_1 where productID does not exist in Table_22b - Update Table_1 where Table_1.ProductID = Table_2.ProductID2c - Insert into Table_1 where Table_2.ProductID does not exist in Table_1My question is: which of the above option is best in terms of PerformanceThe Table_1 would need to be updated 4 times a week.regards |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-03-17 : 05:36:03
|
| How much data is there, and what indexes do you have in place? I'd be inclined to drop Tabel_1 and do a SELECT INTO from Table_2 for performance reasons as this is a minimally logged operation. Clearly, you'd have to recreate any indexes following table creation. What is it you're doing here?Mark |
 |
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2006-03-17 : 05:42:52
|
| I have 600 rows (of course it can grow in future)No indexesBasically, Table_1 & Table_2 contain Product details. If Table_2 is modified, I need to reflect the changes in Table_1. For my web application, only Table_1 is used to get the product details.regards |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-03-17 : 05:49:29
|
| Why two tables?Mark |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2006-03-17 : 06:24:01
|
| As u can imagine, every organisation is different.My org does not deel comfortable working directly with web database. They want to be able to modify product details (description, price, weight) using an old software. Once they are happy with the modifications, I will export the product details into a csv file and then update temporary web table (Table_2). Then I will update Products web table (Table_1) using Table_2regards |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-03-17 : 07:55:24
|
Hi hasanali00You could use replication of course, but given your description, I think I'd probably keep it simple and go for what mwjdavidson suggests, or for your first option. It sounds like it won't matter too much what option you choose though.  Ryan Randallwww.monsoonmalabar.comIdeas are easy. Choosing between them is the hard part. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-17 : 13:05:27
|
| Will it matter that for a short time there will be zero products if you DELETE and then INSERT INTO (you could put a TRANSACTION around it, but then the table will be unavailable)For 600 rows (or even a few thousand) a Delete Everything and Re-Insert will be fine. It also ensures that the Main Table **IS** an identical copy.Yeah, you'd think so wouldn't you, but the UPDATE USET ... all columns ...FROM MainTable AS UJOIN SpareTable AS S ON S.ID = U.IDWHERE ... rows are different ..has some problems:1) You ought to do a case sensitive compare - you might not know about that/forget/etc!2) You can't compare TEXT columns easily3) If you add a new column to the table you might forget to add it to the "compare" - or even the SET column list.Having said that, for a large table with relatively few rows, especially if it is needed to be "available" during the process, then DELETE obsolete rows, UPDATE changed rows and INSERT new rows is a good process.Kristen |
|
|
|
|
|
|
|
|
Updating a table - the best option