Monday, March 12, 2012

getting primary key out of insert into statement

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