NEED Some MSSQL HELP!!!!

Ok here is the situation. I am trying to create a procedure to find and remove double entries in the database at the end of every month by itself. The Database program is MSSQL 2000 Enterpise. Now things would be a lot easier if the idiot who build this database was a bit brighter. There are five tables that come into play, meaning I need information from all five. Now here is the tricky part, two out of the five tables have no relationship with the other three. Meaning they have no PK or FK connection (notice how smart that idiot was!). Now to draw the picture in further detail the two tables that have no relationship are called "tblAddresses" and "tblInternetAddresses". The other three are "tblContacts","tblDemograpics", and "tblClientContacts".

Now atleast there still a bit hope, in "tblAddresses" there is an PK name "AddressID" and in "tblInternetAddresses" there is an PK name "InternetAddressesID". These two columns are also in the "tblContacts".

Yet here is the catch, both the "tblAddresses" and "tblInternetAddresses" have the data from "tblContacts", but they have that and more. This is because due to the fact of non-relation, what gets remove from "tblContacts does not effect "tblAddresses" and "tblInternetAddresses". But this is not the main problem I am here to discuss. I am only explaining this to have you understand what I am dealing with.

Now the other three are connected to each other with the help of the PK "ContactID" on the "tblContacts". So this kinda makes "tblContacts" the center of attention. Which mean if I do remove doubles, it will be throught this table.

I think that should be enough information to be explain by words. If you need more detail you can email me at "alex@mmgusa.com" I will now show you the codes of what I am trying to create to eliminate the doubles in the "tblContacts":
--------------------------------Beginning of Codes-------------------------
Declare @ConID INT,
@FN varchar(20),
@LN varchar(20),
@AddID INT,
@InterAddID INT,
@ClienID INT,
@Bday INT,
@Bmonth INT,
@ConID2 INT,
@FN2 varchar(20),
@LN2 varchar(20),
@AddID2 INT,
@InterAddID2 INT,
@ClienID2 INT,
@Bday2 INT,
@Bmonth2 INT,
@PZC nvarchar(20),
@PZC2 nvarchar(20),
@EM nvarchar(20),
@EM2 nvarchar(20),
@counter INT


Declare contacts_cursor Cursor For
Select ContactID,FirstName,LastName,AddressID,InternetAddressID
From tblContacts
Order by ContactID

OPEN contacts_cursor

FETCH NEXT FROM contacts_cursor
INTO @ConID,@FN,@LN,@AddID,@InterAddID

Declare contacts2_cursor Cursor For
Select ContactID,FirstName,LastName,AddressID,InternetAddressID
From tblContacts
Where ContactID != @ConID
Order by ContactID

OPEN contacts2_cursor

FETCH NEXT FROM contacts_cursor
INTO @ConID2,@FN2,@LN2,@AddID2,@InterAddID2

DECLARE clientcontacts_cursor Cursor For
Select ClientID
From tblClientContacts
WHERE ContactID=@ConID

OPEN clientcontacts_cursor

FETCH NEXT FROM clientcontacts_cursor
INTO @ClienID

DECLARE clientcontacts2_cursor Cursor For
Select ClientID
From tblClientContacts
WHERE ContactID=@ConID2

OPEN clientcontacts2_cursor

FETCH NEXT FROM clientcontacts2_cursor
INTO @ClienID2

DECLARE demographics_cursor Cursor FOR
SELECT BirthdayDay,BirthdayMonth
FROM tblDemographics
WHERE ContactID=@ConID

OPEN demographics_cursor

FETCH NEXT FROM demographics_cursor
INTO @Bday,@Bmonth

DECLARE demographics2_cursor Cursor FOR
SELECT BirthdayDay,BirthdayMonth
FROM tblDemographics
WHERE ContactID=@ConID2

OPEN demographics2_cursor

FETCH NEXT FROM demographics_cursor
INTO @Bday2,@Bmonth2

DECLARE addresses_cursor Cursor FOR
SELECT PostalZipCode
FROM tblAddresses
WHERE AddressID=@AddID

OPEN addresses_cursor

FETCH NEXT FROM addresses_cursor
INTO @PZC

DECLARE addresses2_cursor Cursor FOR
SELECT PostalZipCode
FROM tblAddresses
WHERE AddressID=@AddID2

OPEN addresses2_cursor

FETCH NEXT FROM addresses2_cursor
INTO @PZC2

DECLARE InternetAddresses_cursor Cursor FOR
SELECT Email
FROM tblInternetAddresses
WHERE InternetAddressID=@InterAddID

OPEN InternetAddresses_cursor

FETCH NEXT FROM InternetAddresses_cursor
INTO @EM

DECLARE InternetAddresses2_cursor Cursor FOR
SELECT Email
FROM tblInternetAddresses
WHERE InternetAddressID=@InterAddID2

OPEN InternetAddresses2_cursor

FETCH NEXT FROM InternetAddresses2_cursor
INTO @EM2
SET @counter = 0
WHILE @@FETCH_STATUS = 0
IF (@ConID=@ConID2)
BREAK

Else
If (@FN=@FN2)
set @counter = @counter + 1;
If (@LN=@LN2)
set @counter = @counter + 1;
If (@ClienID=@ClienID2)
set @counter = @counter + 1;
If (@Bday=@Bday2)
set @counter = @counter + 1;
If (@Bmonth=@Bmonth2)
set @counter = @counter + 1;
If (@AddID=@AddID2)
set @counter = @counter + 1;
If (@PZC=@PZC2)
set @counter = @counter + 1;
If (@EM=@EM2)
set @counter = @counter + 1;

If (@counter=7 or @counter=8)
USE mmgnet_meridianmarketinggroup_com
Delete
From tblContacts
Where(ContactID=@ConID)


CLOSE InternetAddresses2_cursor
CLOSE InternetAddresses_cursor
CLOSE addresses2_cursor
CLOSE addresses_cursor
CLOSE demographics2_cursor

CLOSE demographics_cursor

CLOSE clientcontacts2_cursor
CLOSE clientcontacts_cursor
CLOSE contacts2_cursor
CLOSE contacts_cursor
----------------------------end of coding----------------------------------

Now this is what the script does when I run it on SQL Query Analyzer. All it do is effect one row, which remove the first row from the "tblContacts". The only logical explanation for this is the counter is always going to 7 or 8. I know something is defenately wrong in the coding, but I don't know how to fix it. Please if anyone can explain my mistake it would be greatly appreciated. If the code on this post is difficult for you to see I have attached a .txt version to this post with indents.

Alex

ps. If you want to get a hold of me for further information please feel free to email me at "alex@mmgusa.com". I will be checking it very frequenly.

The first problem is that you don't have a loop going, which is why you're only affecting one row. You're not calling FETCH NEXT inside the loop. See http://msdn.microsoft.com/library/d...lobals_1c1f.asp for an example.

On the other hand, using cursors is highly inefficient. I'm not entirely certain about the structure of your table, but it is probably possible to delete rows using SELECT statements by joining to itself something like this:

DELETE FROM t2
FROM tablename t1
INNER JOIN tablename t2
ON t1.field1 = t2.field1
AND t1.id < t2.id

This is assuming that field1 is the field that is duplicated multiple times and id is a unique field. The above statement will delete all duplicates and only keep the minimum ID. Obviously, if you're going to delete rows from another table, you can join to it as well

DELETE FROM t3
FROM tablename t1
INNER JOIN tablename t2
ON t1.field1 = t2.field1
INNER JOIN tablename t3
ON t2.id = t3.table1_id
WHERE t1.id < t2.id

Hope this helps

Thanks for the tip Scorpions4ever. I will give it a try and let you know what happens.

Update on my coding

Declare @ConID INT,
@FN varchar(20),
@LN varchar(20),
@AddID INT,
@InterAddID INT,
@ClienID INT,
@Bday INT,
@Bmonth INT,
@ConID2 INT,
@FN2 varchar(20),
@LN2 varchar(20),
@AddID2 INT,
@InterAddID2 INT,
@ClienID2 INT,
@Bday2 INT,
@Bmonth2 INT,
@PZC nvarchar(20),
@PZC2 nvarchar(20),
@EM nvarchar(20),
@EM2 nvarchar(20),
@counter INT

DECLARE contacts_cursor CURSOR FOR
SELECT A.ContactID,A.FirstName,A.LastName,A.AddressID,A.InternetAddressID,W.PostalZipCode,X.BirthdayDay,
X.BirthdayMonth,Y.Email,Z.ClientID
FROM tblContacts AS A
INNER JOIN tblAddresses AS W
ON A.AddressID=W.AddressID
INNER JOIN tblDemographics AS X
ON A.ContactID=X.ContactID
INNER JOIN tblInternetAddresses AS Y
ON A.InternetAddressID=Y.InternetAddressID
INNER JOIN tblClientContacts AS Z
ON A.ContactID=Z.ContactID
ORDER BY A.ContactID

OPEN contacts_cursor
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM contacts_cursor
INTO @ConID,@FN,@LN,@AddID,@InterAddID,@PZC,@Bday,@Bmonth,@EM,@ClienID

-----Begining INNER LOOP------
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM contacts_cursor
INTO @ConID,@FN,@LN,@AddID,@InterAddID,@PZC,@Bday,@Bmonth,@EM,@ClienID

FETCH NEXT FROM contacts_cursor
INTO @ConID2,@FN2,@LN2,@AddID2,@InterAddID2,@PZC2,@Bday2,@Bmonth2,@EM2,@ClienID2
SET @counter=0

IF (@ConID=@ConID2)
BREAK
Else
If (@FN=@FN2) AND (@LN=@LN2)
set @counter = @counter + 1;

If (@ClienID=@ClienID2)
set @counter = @counter + 1;

If (@Bday=@Bday2) AND (@Bmonth=@Bmonth2)
set @counter = @counter + 1;

If (@AddID=@AddID2)
set @counter = @counter + 1;

If (@PZC=@PZC2)
set @counter = @counter + 1;

If (@EM=@EM2)
set @counter = @counter + 1;

If (@counter>3)
USE mmgnet_meridianmarketinggroup_com
DELETE
FROM tblContacts
WHERE(ContactID=@ConID)
END
----End INNER LOOP------

FETCH NEXT FROM contacts_cursor
INTO @ConID2,@FN2,@LN2,@AddID2,@InterAddID2,@PZC2,@Bday2,@Bmonth2,@EM2,@ClienID2

SET @counter=0

IF (@ConID=@ConID2)
BREAK

Else
If (@FN=@FN2) AND (@LN=@LN2)
set @counter = @counter + 1;

If (@ClienID=@ClienID2)
set @counter = @counter + 1;

If (@Bday=@Bday2) AND (@Bmonth=@Bmonth2)
set @counter = @counter + 1;

If (@AddID=@AddID2)
set @counter = @counter + 1;

If (@PZC=@PZC2)
set @counter = @counter + 1;

If (@EM=@EM2)
set @counter = @counter + 1;

If (@counter>3)
USE mmgnet_meridianmarketinggroup_com
DELETE
FROM tblContacts
WHERE(ContactID=@ConID)
END
CLOSE contacts_cursor

I still have problem get row 1vs3 and 1vs4 so on ... then 2vs3 and 2vs4 so on .....