EXISTS does not work with INSERT

Question

Post reply

EXISTS does not work with INSERT

PatrickSimons

Right there with Babe

Points: 724
More actions
December 12, 2007 at 2:03 am

#62894

Example:
INSERT INTO Table1 (CustID, CustName)
SELECT DISTINCT CustID, CustName
FROM ##TThird T1
WHERE NOT EXISTS(
SELECT CustID
FROM Table1
WHERE CustID = T1.CustID)
Because the ##TThird is filled by another app (which is not so proper), I could have the following data:
CustID CustName
01 Phil_1
01 Phil_2
02 Alex
With this data I got a violation of Primary Key...
Why is the where clause with "NOT EXISTS" not trapping the double ID in the temp table?
Tx
Patrick
Patrick SIMONS, MCP

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 1

Hi Simons,

the only way is to use a cursor or the the code below.

INSERT INTO Table1 (CustID, CustName)

SELECT DISTINCT CustID, CustName

FROM ##TThird T1

WHERE CustName=(SELECT top 1 CustName FROM ##TThird T2 WHERE T1.custid=T2.custid)

Regards,

Ahmed

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

INSERT INTO Table1 (CustID, CustName) SELECT DISTINCT CustID, CustName FROM ##TThird T1 WHERE NOT EXISTS(SELECT top 1 CustID FROM ##TThird T2 WHERE T1.custid=T2.custid)

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 3

Hi,

I am sorry Vyas find another way 😛

Regards,

Ahmed

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Your PK is on CustID?

At the point that the exists runs, the row

01 Phil_1

is not in Table1, hence the second row twith aa 01 id si not filtered out.

The distinct doesn't throw it out, because the customer name is different than the other record with an id of 01

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PatrickSimons Right there with Babe Points: 724 More actions · Answer 5

I think your approaches should work (I didn't test it yet), but nobody answered my question: why does my SubSelect on Table1 not work?

Is the SQL-Engine not so intelligent to see if 1 row has been already added to Table1?

Tx

Patrick SIMONS, MCP

PatrickSimons Right there with Babe Points: 724 More actions · Answer 6

Gila had the answer.

But, is there no way to refresh in some way the SubSelect while inserting the rows? Some special syntax....?

Tx,

Patrick SIMONS, MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Edit: Cross-post...

SQL doesn't insert one row at a time. It inserts the entire batch at once The Where clause (with exists) executes before the insert occurs. The operations in the insert run in the following order

FROM -- get all rows from the ## table

WHERE -- eliminate the ones not wanted.

DISTINCT -- remove duplicates from resulting set

INSERT -- all rows in the set at once.

To answer your question, no. To do what you want will require a cursor-type approach, inserting each row individually

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 8

PatrickSimons (12/12/2007)
I think your approaches should work (I didn't test it yet), but nobody answered my question: why does my SubSelect on Table1 not work?
Is the SQL-Engine not so intelligent to see if 1 row has been already added to Table1?

Important notes for you:

1) there is no 1st row, as well as 2nd , 3rd, etc. Unless you have it explicitly numbered.

2) 1st row has not been added. All rows are getting added together, in the same transaction.

Before you start INSERT you need to decide what to do with those rows having duplicated key.

Reject both of them? Insert one of them? Which one then?

When you've answered these questions we can help you with a solution.

Answering the questions don't forget about the note 1) above.

_____________
Code for TallyGenerator

GSquared SSC Guru Points: 260824 More actions · Answer 9

change your select clause to "Select custid, min(custname)" instead of using distinct, and it will work. Will be slower, because of the aggregate, but won't cause an error. Should be faster than a cursor still.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon