December 12, 2007 at 2:03 am
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
December 12, 2007 at 3:04 am
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
December 12, 2007 at 3:25 am
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)
December 12, 2007 at 3:31 am
Hi,
I am sorry Vyas find another way 😛
Regards,
Ahmed
December 12, 2007 at 3:45 am
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
December 12, 2007 at 3:53 am
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
December 12, 2007 at 3:56 am
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
December 12, 2007 at 3:59 am
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
December 12, 2007 at 4:04 am
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
December 12, 2007 at 12:51 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply