October 19, 2009 at 3:22 am
Hi
I have been trying to deal with a bug in a stored procedure which gives the error of primary key violation.
INSERT INTO TableA(NumberA, NumberCodeID, RouteID, DateUpdated, PlatformID, Grade, Notes)
SELECT NumberA, @NumberCodeID, null, null, @PlatformID, -1, null
FROM #NewNumbers a
WHERE NOT EXISTS (SELECT TOP 1 1 FROM dbo.TableA WITH(NOLOCK) WHERE TableA.NumberA = a.NumberA AND NumberCodeID = @NumberCodeID)
I am already checking if the row exists, and only insert the row if it does not exists. However, it still throws a primary key violation which is (NumberA, NumberCodeID) for TableA.
Also, even if there were duplicates in the temp #NewNumbers table, I believe this query would have still taken care of the duplicates.
Any clues as to where to look ?
October 19, 2009 at 4:16 am
You probably have duplicates in the SELECT statement.
As you have not given us the PK, the best I could suggest is to try SELECT DISTINCT...
October 19, 2009 at 4:52 am
Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??
The Primary key for TableA is mentioned in the above, but I am putting it here as well
(NumberA, NumberCodeID)
October 19, 2009 at 5:14 am
namrata.dhanawade-1143388 (10/19/2009)
Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??
No - the exists checks that the PK is not already in the table. You need to ensure that any new PKs do not occur more than once in the SELECT statement.
-- *** Test Data ***
CREATE TABLE #temp
(
PK int NOT NULL PRIMARY KEY
)
INSERT INTO #temp
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
CREATE TABLE #New
(
New int NOT NULL
)
INSERT INTO #New
SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 5
-- *** End Test Data ***
-- This will fail as 2 and 3 are already in #temp
INSERT INTO #temp
SELECT New
FROM #New
-- This will fail as 4 occurs twice
INSERT INTO #temp
SELECT New
FROM #New N
WHERE NOT EXISTS
(
SELECT *
FROM #temp T
WHERE T.PK = N.New
)
-- This will work
INSERT INTO #temp
SELECT DISTINCT New
FROM #New N
WHERE NOT EXISTS
(
SELECT *
FROM #temp T
WHERE T.PK = N.New
)
October 19, 2009 at 5:18 am
namrata.dhanawade-1143388 (10/19/2009)
Even if there are duplicates in the select, will it not get caught in the if exists for the second insertion/ duplicate insertion ??
Insert ... select does not work row by row. It does not run the select, take the first row, insert it, run the select, take the second row, insert it,....
Insert... select works by first getting the entire resultset returned by the select and then inserting all of those rows in a single operation. By the time the insert starts, the select (with it's associated EXISTS) has been evaluated and won't be evaluated again.
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
October 19, 2009 at 5:26 am
Thanks to both of you. This has really helped.
I think, the question really was whether bulk inserts happen one row at a time. And since the answer is NO, the distinct will probably solve the problem.
🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply