Dirty Read ?

[RSMEINER]

I'm having a little problem with using Select Not Exists between 2 tables.
I get the infamous Primary key "Cannot insert duplicate key" error.
This is on MS Sql 2000.

I'm trying to create a temp table with distinct values from another table.

Heres a small snippet of the code.

Create table ##tblMail
(ClientID Integer)
Insert into ##tblMail (ClientID) Values (1)
Insert into ##tblMail (ClientID) Values (1)
Insert into ##tblMail (ClientID) Values (2)
Insert into ##tblMail (ClientID) Values (2)

Drop Table ##TmpMail
GO
Create Table ##TmpMail
(ClientID Integer
Primary Key (ClientID))
GO

Insert into ##TmpMail
(ClientID)
select Mail.ClientID
from ##tblMail Mail
where NOT EXISTS (SELECT DISTINCT(##TmpMail.ClientID)
FROM ##TMPMAIL
WHERE Mail.ClientID = ##TmpMail.ClientID)

Thanks

Randy
http://members.aol.com/rsmeiner

[Simon Hayes]

"RSMEINER" <rs******@aol.comcrap> wrote in message
news:20***************************@mb-m25.aol.com...

I'm having a little problem with using Select Not Exists between 2 tables.
I get the infamous Primary key "Cannot insert duplicate key" error.
This is on MS Sql 2000.

I'm trying to create a temp table with distinct values from another table.

Heres a small snippet of the code.

Create table ##tblMail
(ClientID Integer)
Insert into ##tblMail (ClientID) Values (1)
Insert into ##tblMail (ClientID) Values (1)
Insert into ##tblMail (ClientID) Values (2)
Insert into ##tblMail (ClientID) Values (2)

Drop Table ##TmpMail
GO
Create Table ##TmpMail
(ClientID Integer
Primary Key (ClientID))
GO

Insert into ##TmpMail
(ClientID)
select Mail.ClientID
from ##tblMail Mail
where NOT EXISTS (SELECT DISTINCT(##TmpMail.ClientID)
FROM ##TMPMAIL
WHERE Mail.ClientID = ##TmpMail.ClientID)

Thanks

Randy
http://members.aol.com/rsmeiner

Insert into ##TmpMail
(ClientID)
select distinct ClientID
from ##tblMail m
where NOT EXISTS (SELECT *
FROM ##TMPMAIL t
WHERE m.ClientID = t.ClientID)

Simon

[RSMEINER]

>Insert into ##TmpMail

(ClientID)
select distinct ClientID
from ##tblMail m
where NOT EXISTS (SELECT *
FROM ##TMPMAIL t
WHERE m.ClientID = t.ClientID)

Simon

I got pretty excited there for a second.
Remember I said it was just a snippet ?

Heres the rest

Insert into ##TmpMail
(ClientID,
ScheduleID,
MSType,
ScheduleType,
CarrierCode,
Address1,
Address2,
Address3,
Address4,
Address5,
Address6,
JunkMailCode)
select Mail.ClientID,
Mail.ScheduleID,
MSType,
ScheduleType,
CarrierCode,
Address1,
Address2,
Address3,
Address4,
Address5,
Address6,
JunkMailCode
from ##tblMail Mail
Join tblMailSetup MS
on Mail.ClientID = MS.ClientID
where ((NOT EXISTS (SELECT DISTINCT(##TmpMail.ClientID)
FROM ##TMPMAIL
WHERE Mail.ClientID = ##TmpMail.ClientID))

Randy
http://members.aol.com/rsmeiner

[Gert-Jan Strik]

The solution is the same.

The NOT EXISTS subquery makes sure that you do not insert rows that
already exist (you don't need the DISTINCT keyword here). In the main
SELECT you need to make sure that you do not add new rows with the same
ID more than once.

If all columns have the same values for the duplicate rows, then you can
still use DISTINCT in your main query (INSERT INTO ... SELECT DISTINCT
.... FROM ...), as suggested by Simon in the previous post.

If not, then the easiest way is to clean the data first before you do
the insert.

HTH,
Gert-Jan


RSMEINER wrote:

Insert into ##TmpMail
(ClientID)
select distinct ClientID
from ##tblMail m
where NOT EXISTS (SELECT *
FROM ##TMPMAIL t
WHERE m.ClientID = t.ClientID)

Simon

I got pretty excited there for a second.
Remember I said it was just a snippet ?

Heres the rest

Insert into ##TmpMail
(ClientID,
ScheduleID,
MSType,
ScheduleType,
CarrierCode,
Address1,
Address2,
Address3,
Address4,
Address5,
Address6,
JunkMailCode)
select Mail.ClientID,
Mail.ScheduleID,
MSType,
ScheduleType,
CarrierCode,
Address1,
Address2,
Address3,
Address4,
Address5,
Address6,
JunkMailCode
from ##tblMail Mail
Join tblMailSetup MS
on Mail.ClientID = MS.ClientID
where ((NOT EXISTS (SELECT DISTINCT(##TmpMail.ClientID)
FROM ##TMPMAIL
WHERE Mail.ClientID = ##TmpMail.ClientID))

Randy
http://members.aol.com/rsmeiner

--
(Please reply only to the newsgroup)

[RSMEINER]

>The solution is the same.

The NOT EXISTS subquery makes sure that you do not insert rows that
already exist (you don't need the DISTINCT keyword here). In the main
SELECT you need to make sure that you do not add new rows with the same
ID more than once.

If all columns have the same values for the duplicate rows, then you can
still use DISTINCT in your main query (INSERT INTO ... SELECT DISTINCT
... FROM ...), as suggested by Simon in the previous post.

If not, then the easiest way is to clean the data first before you do
the insert.

HTH,
Gert-Jan

Thanks. I will fool with it later today.
Clean data ? Is there such a thing anymore ? This data is
dirty. Plain and simple. I'm redoing a real ugly and old system.
Randy
http://members.aol.com/rsmeiner