Trying to find duplicates in tables

[desireemm]

I need to find out which record I am violating by trying to execute this stored procedure, I am trying to insert the 2004 terms from the TERMINATION table to the Corovan_Table..how do you make a comparison with the two tables??


Code:
ALTER PROCEDURE InsertCorovan2004
AS
INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table]
([TM #],
[FirstName],
[LastName],
[SS #],
[TerminationDate],
[Voluntary or Involuntary])

SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate, VoluntaryorInvoluntary
FROM dbo.TERMINATION
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102))

GO


when I execute it I get this error message

Code:
Server: Msg 2627, Level 14, State 1, Procedure InsertCorovan2004, Line 3
Violation of PRIMARY KEY constraint 'PK_Corovan_Table'. Cannot insert duplicate key in object 'Corovan_Table'.
The statement has been terminated.
Stored Procedure: GamingCommissiondb.dbo.InsertCorovan2004
Return Code = -4

I am trying to insert 2004 terms into the corovan table from TERMINATION table. How do you find out what records match from each table (compare the tables reocords for dups). How would I do this in the query analyzer since thats mainly what I use. I get a better understanding of how to create proceudure in the query analyzer so I;d rather stick to it.

In short I need to find the Culprit record that is preventing me from insert the 2004 records


Thank you

 

[checkai]

This site may help with finding Dup's in SQL

http://www.devguru.com/features/tutorials/AdvancedSQL/advancedSQL.html

 

[dbomrrsm]

whichever is the PK on the insert into table assume say that it is the SocialSecurityNumber then you would do

select SocialSecurityNumber
from TERMINATION
where SocialSecurityNumber IN
(SELECT SocialSecurityNumber from Corovan_Table)

[blue]DBomrrsm[/blue]

 

[donutman]

The technique to do what you want is to create a rowset from "Corovan_Table Right Join Termination." Then check the Corovan_Table column used in the join and check for Null values. Those rows will confirm that the Termination data isn't in the Corovan_Table. Does that make sense to you? If not define your table structure so that we don't have to guess about which column to use in the join and the date column to filter on for the 2004 value.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]