In my target table, where I want to insert the records from my text file, the record with primary key EDF010 already exists.
In my text file I have the following rows:
EDF800,New Pradera,Zepita 123,L-01,450,430,Perímetro de Lima,EST001
EDF900,New Oh Sol,Malecón Checa 357,L-14,178,158,Perímetro de Lima,EST001
EDF010,New Nogal,Josè Pizarro,L-21,246,226,Perímetro de Lima,EST001
Is there a way to validate the duplicate records so that only the duplicates are not inserted and the error is not thrown?
Msg 2627, Level 14, State 1, Line 15
Violation of PRIMARY KEY constraint 'PK__EDIFICIO__CE4324755BE55BAA'. Cannot insert duplicate key in
object 'Renta.EDIFICIOS'. The duplicate key value is (EDF010).
The statement has been terminated.
Not from the
BULK INSERT
. What you can do:BULK INSERT
insert all into a temporary tableCOD_EFIF
did not exist.Something like that:
And now, having the temporary is everything simpler:
Final comment:
Actually, I think I lied to you about the
BULK INSERT
, since according to the documentation there would be a way, which is by using the modifierMAXERRORS
, I have never used it nor would I, but many on SO sites suggest it as an alternative, giveMAXERRORS
a very high value to ignore any duplicate errors and eventually any others.