code_junkie

What is the best way, algorithm, method to difference large lists of data

Nov 13th, 2011
139
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.91 KB | None | 0 0
  1. INSERT AccountsAdded(AccountNum, Name) SELECT AccountNum, Name FROM AccountsNew WHERE AccountNumber not in (SELECT AccountNum FROM AccountsCurrent)
  2.  
  3. INSERT AccountsRemoved(AccountNum, Name) SELECT AccountNum, Name FROM AccountsCurrent WHERE AccountNumber not in (SELECT AccountNum FROM AccountsNew)
  4.  
  5. TRUNCATE TABLE AccountsCurrent
  6.  
  7. INSERT AccountsCurrent(AccountNum, Name) SELECT AccountNum, Name FROM AccountsNew
  8.  
  9. TRUNCATE TABLE AccountsNew
  10.  
  11. INSERT INTO [table] (
  12. [fields]
  13. )
  14. SELECT
  15. [fields]
  16. FROM
  17. [table1]
  18. LEFT JOIN
  19. [table2]
  20. ON [join condition]
  21. WHERE
  22. [table2].[id] IS NULL
  23.  
  24. INSERT INTO [table] (
  25. [fields]
  26. )
  27. SELECT
  28. [fields]
  29. FROM
  30. [table1]
  31. WHERE
  32. EXISTS (SELECT * FROM [table2] WHERE [condition to match tables 1 and 2])
  33.  
  34. -- # This is quick provided that all fields to match the two tables are
  35. -- # indexed in both tables. Should then be much faster than the IN clause.
Add Comment
Please, Sign In to add comment