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 |
|
dbleyl
Starting Member
21 Posts |
 Posted - 2002-02-08 : 16:06:00
|
| Ok hi,Merging a table into another is a fairly common business: you have some key and a count column, and you want to add a new row if the key doesn't exist or combine the appropriate fields if the key's already there. Simple enough.Usually, you can join Tables A & B, and update A set A.C = A.C + B.C.Then turn around and Insert B into A for every row in B that doesn't exist in A. (I think it's the same as MERGE keyword in 9i) OK.If A & B are temporary tables, though, with 5 million rows each, and the merge will be performed an arbitrary # of times,(with B being discarded each time, and A after the group of merges)does anyone have any tips on speeding this kinda thing up? SQL 7,sp3.I'm testing different indexes, right now, obviously, there's some tables scans goin on.How about dumping all the rows into A until all the B's are processed, then inserting A into a final table w/ group by & sum?I'm trying different methods as I post this, but time is critical...Thanks,Don |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-08 : 16:17:05
|
| How's this idea?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12776This would handle the INSERT portion, not the UPDATE. It might be a dog using temp tables without indexes, especially if they're 5 million+ rows.The UPDATE you can do with a simple JOIN:UPDATE A Set A.col1=B.col1FROM A INNER JOIN B ON (A.keyCol=B.keyCol) |
 |
|
|
dbleyl
Starting Member
21 Posts |
Posted - 2002-02-08 : 16:40:52
|
| Thanks robvolk,This is what i've got thus far:UPDATE NotSoTempA SET [Count] = C.[COUNT] + T[COUNT] FROM NotSoTempA as C INNER JOIN #TempB as T ON C.K1 = T.K1 AND C.K2 = T.K2 AND C.K3 = T.K3--Insert the rows that don't exist.INSERT INTO NotSoTempA SELECT K1, K2, K3, AGE, [COUNT] FROM #TempB as T WHERE NOT EXISTS( SELECT * FROM NotSoTempA as C WHERE T.K1 = C.K1 AND T.K2 = C.K2 AND T.K3 = C.K3 ) This works, but it works slowly. I'm trying 2 different things now: Adding indexes, and dumping all the data into A, then inserting A into another table w/ GROUP BY K1,K2,K3 & SUM([COUNT]) |
|
|
|
dbleyl
Starting Member
21 Posts |
Posted - 2002-02-08 : 18:12:01
|
| OK, dumping all the inserts into one table then grouping & Sum(COUNT) blew away the Insert/Update method. Seems like I've been down this road a couple of times... |
|
|
|
|
|
|
|
|
Conditionally Merge rows tips?