sql server - Ignore warning in Stored Procedure / Prevent procedure from stopping -
i manage database collective 40 different individual servers identical structures own unique data. have multiple stored procedures on central system bulk insert .txt files compiled these servers tables.
due 40 different off-site servers, there times data missed due packet loss. question is, there anyway have procedure ignore warning , continue processing? in particular case, skip on errors such as:
msg 547, level 16, state 0, line 36 insert statement conflicted foreign key constraint "fk_appointmentpatient". conflict occurred in database "eaglesoft", table "patient.patient".
i realize it's throwing error because primary key looking not in corresponding table foreign key relation. i'd update data possible during automated process , check logs afterward , fix individual issues afterward. other jobs. here stored procedure being used:
create table patient.tempappointment --create temporary table data ( clinicid int null, appointmentid int null, starttime datetime null, endtime datetime null, patientid int null, locationid tinyint null, typeid int ) bulk insert patient.tempappointment --insert data temporary table 'e:\data\patientappointment.txt' ( fieldterminator='|', rowterminator='\n', maxerrors = 99 ) insert patient.appointment --insert data main table temporary table data doesn't exist select * patient.tempappointment not exists (select * patient.appointment (patient.tempappointment.appointmentid=patient.appointment.appointmentid , patient.tempappointment.clinicid=patient.appointment.clinicid)) , patient.tempappointment.appointmentid not null , patient.tempappointment.patientid not null drop table patient.tempappointment --drop temporary table
you can temporary disable checking foreign keys , after import cleanse data.
exec sp_msforeachtable "alter table ? nocheck constraint all" if it's live server copy database structure, import data temporary database after succesfull cleansing fill data live.
Comments
Post a Comment