18262b73   
   From: esquel@sommarskog.se   
      
   Richard (richardemig@gmail.com) writes:   
   > I have a table (Main) with compound key and second, temporary table   
   > (Temp) with same fields but without PK. Main table key is 3 columns   
   > ( SourceFileHash, FileHash, PathHash ). There is also 4 other columns   
   > in each table.   
   > My intent is to use temp table to store batches of records with   
   > duplicates and inserting only unique records to main table after every   
   > batch is finished ( batches are inserted using SqlBulkCopy ).   
   >   
   > How can I select distinct records on Temp table but using only 3 PK   
   > columns ?   
   >   
   > Here is query that I created - it won't work because it is trying to   
   > insert duplicate records to main:   
      
   WITH numbered AS (   
       SELECT sourceFileHash, pathHash, fileHash, length, creationDate,   
              modificationDate, lastAccessDate, line,   
              row_number() OVER   
                (PARTITION BY sourceFileHash, pathHash, fileHash   
                 ORDER BY  ?????) as rowno   
       FROM   CatalogEntriesTemp   
   )   
   SELECT sourceFileHash, pathHash, fileHash, length, creationDate,   
          modificationDate, lastAccessDate, line   
   FROM   numbered   
   WHERE  rowno = 1   
      
   You need to fill in the ORDER BY clause to determine which of the   
   rows with the same duplicate key you want.   
      
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)