Is there a way to get the primary key that is causing a primary key violation when inserting via a insert into with a select statement. This violation is being caught using a try/catch statement. Is there anyway, short of doing yourself via brute force coding, to get what the values are that are causing the error. Basically, I'm doing:
begin try
insert into TableA
select col1, col2, col3
from TableB
end try
begin catch
--Catch occasional primary key violation.
end catch
not sure if this is what you want to do, but have you considered something like the following?
lets say you have a schema like this (psuedo metadata for brevity sake) and you want to insert table1 into table2
[NB: may have errors as I didn't run this through QA, but the concept is sound]
legend: TableName , primary key fields
table1
A1
A2
A3
table2
B1
B2
B3
then the table1 records that will be a violation can be selected via
select distinct table1.* into #violations
from table1 inner join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2
Then you can insert the valid records via:
insert into table2
select table1.*
from table1 left join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2
where table2.B1 is null
then, you can do
select * from #violations
and do what ever you want with these - write to another table/send in an email, etc. . .
lastly you will want to delete the temporary #violations table
drop table #violations
|||Check out the OUTPUT option of the INSERT statement.
No comments:
Post a Comment