469,591 Members | 1,519 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,591 developers. It's quick & easy.

Dirty Read ?

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
Jul 20 '05 #1
4 5236

"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
Jul 20 '05 #2
>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
Jul 20 '05 #3
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)
Jul 20 '05 #4
>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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Sean C. | last post: by
1 post views Thread by RIP662 | last post: by
9 posts views Thread by Susan Bricker | last post: by
5 posts views Thread by Nmx | last post: by
28 posts views Thread by sowmiyakc18 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.