title: I've searched the SQL Server questions using "INTERSECT" as the key word and nothing came up that helped
body: <p>I have two tables in two databases. One is in a "primary" database, the second is in an "archive" database. The structures are identical, only the data is different. The issue is the primary table has records that are duplicated in the archive table. I need to remove the duplicates from the primary table before archiving records to the archive table. The first attempt seemed to work well, but the activity log filled up, killing the process because there was simply too much going on. The DBAs told me to delete in chunks of 100,000. </p>
  
  <p>I also found out that there may be differences in the records even though the primary keys match between the databases. These records need to be saved, not deleted as duplicates. This led me to the INTERSECT and EXCEPT clauses. I've tried these three samples, and even though each INTERSECT returns 100,000 records, the DELETE only deletes 94,000 to 95,000 records. I included some comments for each. The last one deletes <em>everything</em> from the primary database table.</p>
  
  <pre><code>-- Test #1: Doesn't delete 100,000 - around 94,000 or 95,000
  DELETE TOP (100000) FROM [db_1].[table_1]
  WHERE EXISTS    (
                      -- Returns 100,000
                      SELECT * FROM [db_1].[table_1]
                      INTERSECT
                      SELECT * FROM [db_2].[table_1]
                  )
  
  -- Test #2: Doesn't delete 100,000 - around 94,000 or 95,000
  --          [test_data] returns 100,000 records
  ;WITH [test_data] AS    (
                              -- Returns 100,000
                              SELECT TOP (100000) * 
                              FROM    (
                                          SELECT * FROM [db_1].[table_1]
                                          INTERSECT
                                          SELECT * FROM [db_2].[table_1]
                                      ) [meh]
                          )
  DELETE TOP (100000) FROM [db_1].[table_1]
  WHERE EXISTS    (
                      SELECT * FROM [test_data]
                  )
  
  -- Test #3: Deletes everything from [db_1].[table_1]
  --          The EXISTS clause returns 100,000
  --          The DELETE statement deletes everything from [db_1].[table_1]
  DELETE FROM [db_1].[table_1]
  WHERE EXISTS    (
                      SELECT TOP (100000) [meh].[address_rid]
                          ,[meh].[REV] 
                      FROM    (
                                  -- Returns 100,000
                                  SELECT * FROM [db_1].[table_1]
                                  INTERSECT
                                  SELECT * FROM [db_2].[table_1]
                              ) [meh]
                  )
  </code></pre>
  
  <p>Is this enough information, or should I add more? Thanks in advance...</p>
  
  <p><strong>* EDIT *</strong>
  Crap!.OK, I appreciate the feedback and will try to be more thorough in my explanation. I need to delete records that are duplicated between the two database tables. With the requirement to check each record to make sure it is a true duplicate, no data changed while in the primary database, I found INTERSECT. What I understand is that it does a field-to-field comparison, verifying whether or not a record is a true duplicate between the database tables. Once that has been established, I want to delete the duplicated record from the <em>primary</em> database, leaving the other record in the <em>archive</em> database...</p>
  
  <pre><code>Primary database:        Archive database:
  table_1:                 table_1:
  PK     f_1   f_2   f_3   PK   f_1   f_2   f_3
   1     A     B     B      1   A     B     B
   2     B     D     X      2   B     D     X
   3     C     G     Y     13   Q     M     O
   4     D     J     Z     14   S     M     K
  </code></pre>
  
  <p>In the above table sets, the duplication is with records 1 and 2. Since they both exist in both tables, I need to delete them from the primary database table. What I'm running into is some tables have millions of records and they need to be deleted in 100,000 record chunks, as per our DBAs. My thinking was that if I put the INTERSECT in the EXISTS clause, limiting the results of the INTERSECT to 100,000 records, the DELETE would delete that 100,000 record chunk. But it doesn't. It deletes maybe 94,000 to 95,000 records, then as it nears the end of the deletions, it starts deleting a few thosand at a time, winding down to a handful of records. </p>
  
  <p>If I'm using it incorrectly, or if there's a better way to delete the duplicated records I'm all ears. As several of you have pointed out, I have a difficult time asking the questions correctly, which is why I don't ask very many anymore. I do appreciate any suggestions, comments advice and/or critiques. I'm not so proud as to reject any help. Unless it's a verbal attack on my dog. Then I might get upset! ;-) I hope this helps. If not, I'm sure I'll hear about it.</p>