Hello,

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:

SELECT temp.sourceFileHash, temp.pathHash, temp.fileHash, temp.
[length], temp.creationDate, temp.modificationDate,
temp.lastAccessDate, temp.line
FROM CatalogEntriesTemp temp
LEFT OUTER JOIN CatalogEntries main ON main.pathHash = temp.pathHash
and main.fileHash = temp.fileHash AND main.sourceFileHash =
temp.sourceFileHash
WHERE main.pathHash IS NULL AND main.fileHash IS NULL AND
main.sourceFileHash is null

Thanks for any help in advance.

Richard wrote:
You need to create a CTE or view that uses GROUP BY to select the unique
rows from CatalogEntriesTemp, aggregating the non-key columns so that a
single row is retrieved.

On Jun 7, 8:07*am, Richard <richarde... (AT) gmail (DOT) com> wrote:
It looks to me as if you need to look at your application and
assumptions about your data more closely.

If the key fields actually are hash values, the key duplication could
be coming up because of unresolved hash colisions, So you need first
to make sure that hash the values actually are getting rehashed when
necessary to point to the correct entries.

If the hashing is correct, then, as is likely, your data aquisition
proceess has records which match on the three key fields but have
different values in the other four fields, then before you can get
down to distinct records you need to establish which values for these
other four fields are to be treated as correct in your application.
Once you have completed your application definition you will be in a
position to work out how to write the query.

Fred.

Richard (richardemig (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

Thanks Erland.