September 30, 2008 at 9:50 am
Hi,
I'm inserting multiple records into a table that has a primary key with a constraint. On my insert I'm getting a 'Violation of Primary Key Constraint' error message. If there anyway I can determine what record it is complaining about when I'm doing the insert so I can further investigate?
Thanks
September 30, 2008 at 10:16 am
That depends on how you are inserting them. Can you show us the code?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 11:09 am
I added an insert at the beginning of this select statement to see if I could find out what the problem was and ran it from SQL Server Management Studio. The code below the insert is what I'm using to insert records using SSIS which gives me the same error.
INSERT INTO [PSS_STAGING].[dbo].[party]
SELECT DISTINCT
T1.PARTY_ID
,PARTY_TYP_ID =
CASE
WHEN PARTY_TYPE = 'I' THEN 1
WHEN PARTY_TYPE = 'C' THEN 2
END
,GETDATE() AS CREATED_DTM
,'Migration' AS LST_UPDT_USERID
,GETDATE() AS LST_UPDT_DTM
,'N' AS IS_DELETED_IND
FROM PARTY AS T1
INNER JOIN PARTY_DTL AS T2 ON T1.CLIENT_ID = T2.CLIENT_ID
ORDER BY T1.PARTY_ID
September 30, 2008 at 11:22 am
You would need an additional query to find the duplicate Primary Keys, or you will need a WHERE clause to pre-filter the duplicates out. If you can tell us what the target table's PK is, we can show you how to do that.
Note that the DISTINCT does not completely work here, because you have more columns that just the primary key columns in your SELECT (I assume).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 11:27 am
Thanks. Yes the DISTINCT shouldn't be needed here because there should be no duplicates returned back in the results. The primary Key is Party_Id. That is where the duplicates are. I just wish SQL Server Management Studio would display the record in question when it comes back with the duplicate error message.
September 30, 2008 at 12:05 pm
Here are the queries to find the dupes:
--Find duplicates between source and target
Select T1.PARTY_ID, T1.Client_ID
From PARTY AS T1
Inner Join [PSS_STAGING].[dbo].[party] O1
ON O1.Party_ID = T1.Party_ID
--Find duplicates in source:
Select T1.PARTY_ID, Min(T1.Client_ID), Max(T1.Client_ID), count(*)
From PARTY AS T1
INNER JOIN PARTY_DTL AS T2 ON T1.CLIENT_ID = T2.CLIENT_ID
Group By T1.Party_ID
Having count(*) > 1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 12:16 pm
dale_keller (9/30/2008)[hrI just wish SQL Server Management Studio would display the record in question when it comes back with the duplicate error message.
Yeah, but what if there were a million rows in conflict? What should it do with them in that case?
The problem is that there is no way to return a dataset in an error message. And you don't want to just return it in the normal dataset return context, because that could be mistaken for valid SELECT data by an app using the query. What we need is explicit syntax to direct an error dataset to something that can hold it.
On the other hand, now that we have multiple output stream facilities like the OUTPUT clause and the MERGE statement, maybe we will get something like this in the future.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply