Error trapping SQL insert for primary key violation
-
Hi
Can someone tell me how to error trpa whic specific record/records that generates primary key violation when trying to do a insert.
I have tried to setup SQL Profiler to trap this but I cant get it to report which record that fails
Hints ?
-
If you run the statement from QA, you'll get the offending value(s) back with the results
/Kenneth
-
I am assuming that it is a inserting a bunch of records in one shot otherwise, it is fairly simple to catch this with profiler i.e. you are doing "insert into....select...from...where" instead of insert with values. If the later one, profiler will trap that error and you will obviously know which record it is that gave the PK violation error.
If it is "insert into...select...from...", it will not report which record it failed on...instead you can try dumping the records in a temp table and find out the dupes, example:
--declare temp table variable
--do not declare the PK_COL as the primary key column
--in this case
declare @TEMP table (ROW_NUM INT IDENTITY, PK_COL int)
--CREATE ANOTHER TABLE WITH THE DUPES
--THAT ARE TO BE INSERTED
declare @DUPES TABLE (COL_VALUE INT)
insert into @Dupes values (1)
insert into @Dupes values (1)
insert into @Dupes values (2)
insert into @Dupes values (3)
insert into @Dupes values (3)
--duplicates being inserted via a
--insert into select from
insert @TEMP (PK_COL)
select col_value from @Dupes
--SHOW THE ACTUAL PK (THE FIRST ONE THAT NEEDS TO BE PRESERVED)
SELECT A.*
FROM @TEMP A
LEFT OUTER JOIN @TEMP B
ON A.PK_COL = B.PK_COL
AND A.ROW_NUM > B.ROW_NUM
WHERE B.ROW_NUM IS NULL
--NOW SHOW THE DUPES
SELECT * FROM @TEMP
WHERE ROW_NUM NOT IN
(
SELECT A.ROW_NUM
FROM @TEMP A
LEFT OUTER JOIN @TEMP B
ON A.PK_COL = B.PK_COL
AND A.ROW_NUM > B.ROW_NUM
WHERE B.ROW_NUM IS NULL)
Hth
-
Thanks...and yes it is a bunch of records with insert into...select from..
Cool, I'll try to use the temp table approach to find it out...
many thanks
Cheers
-
Can you explain a bit about the underlying problem..? I mean, are you trying to find out how to insert a bunch of rows into a table with existing data, but you'r e not sure if among the inserts, there may already exist rows, and if so, those rows shouldn't be inserted, but skipped instead...? All for the purpose of avoiding a PK violation error..?
If that is the case, there are a couple of ways to do that.
/Kenneth
-
If running in 2k or later, you can use a Trigger instead of INSERT and store
the offending value in a table.
Regards,
Hernan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply