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 |
|
HomerJ
Starting Member
21 Posts |
 Posted - 2010-07-07 : 16:41:56
|
| I have a large table (1.4 million records), containing historical sales records [HIST_SALES]. I receive a file each month containing all sales for the last 3 months [NEW_SALES]. I'm asked to update [HIST_SALES] with only the records in [NEW_SALES] that don't already exist in [HIST_SALES].I can do a union to get the distinct list. My problem is that there has to be a better way to update [HIST_SALES] than what I'm doing. Currently I do the following:Make a table [TEMP_TABLE] with the distinct union of [HIST_SALES] and [NEW_SALES].Then I delete all records from [HIST_SALES].Then I insert all records from [TEMP_TABLE] into the now empty [HIST_TABLE].Then I delete all records from [TEMP_TABLE] for space purposes.There has GOT to be a smarter/better way to do this?EDIT:Both HIST_SALES and NEW_SALES are identical layouts. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-07 : 18:10:45
|
| If you're using SQL Server 2008, look at the MERGE command, it's tailor-made for this scenario. |
 |
|
|
CrazyT
Yak Posting Veteran
73 Posts |
Posted - 2010-07-07 : 18:23:13
|
| couldnt you do two querys:one that select records that already exist into a temp table and do a join to updateUPDATE SET value= valueFROM historyInner JOIN #temphistory temphistoryON history.ID = temphistory.ID then do one that selects the records that do not exist and do the insert |
|
|
|
|
|
|
Best way to add 'new' records?