Find if Dups exist between two tables

[bmpsu]

I have a stored procedure that executes a BULK INSERT of a flat file into a temp table. Upon some validation checks, I then INSERT this data into a database table; truncating the original data first. This is all done in a transaction that can be rolled back in case of an error.

I have come across the situation where I will not need to truncate the original data first. I need to append the data to certain tables. I need to be able to check if I am getting rows in my flat file that would result in duplicates in the data I already have in my database.

I figured that once I have the flat file data in my temp table, I could query this temp table and the actual table to find if dups exist.

Is this a smart or efficient way to do this and if so, how would I write that query?

Sql Server 2005

Thanks

 

[hmckillop]

have a look at this, should give you everything you need.
http://www.15seconds.com/issue/011009.htm

"I'm living so far beyond my income that we may almost be said to be living apart

 

[bmpsu]

Thank you for your response. This article seems to work if I know the tables I am working with. The SP that I use that performs this data import is used for many tables. So my SP does not know column names or existing keys. All my SP has are two identical table structures with data. One being a temp table and the other is the actual db table I would like to replace or append my temp table data to.

Will this be possible?

 

[gmmastros]

Take a look at the Binary_Checksum function. Look it up in Books On Line.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmpsu]

I looked into the Binary_Checksum function and checksum_agg function. These functions would work great on telling me that my tables are different. In this case, my tables will always be different. Since my current table is constantly having data appended to it.

This is what I am testing right now. I created a SP that will query my temp table with my current table using a UNION ALL. I am able to retrieve the number of rows that are different between my temp table and current table. As long as this number equals the number of rows in my temp table, then all the rows in the temp table should be new or unique.

Does anyone see anything potentially wrong with this.