Ignore warning in Stored Procedure / Prevent procedure from stopping

Question

I manage a database that is a collective from 40 different individual servers with identical structures but their own unique data. I have multiple stored procedures on my central system that does a BULK INSERT from .txt files compiled from these servers for their tables.

Due to the 40 different off-site servers, there are times where some data is missed due to packet loss. My question is, is there anyway to have my procedure ignore a warning and continue its processing? In this particular case, I would like to skip over errors such as:

Msg 547, Level 16, State 0, Line 36 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AppointmentPatient". The conflict occurred in database "Eaglesoft", table "Patient.Patient".

I realize it's throwing this error because the primary key it is looking for is not in the corresponding table for the foreign key relation. However I'd like to update as much data as possible during my automated process and check the logs afterward and fix the individual issues like this afterward. Other than all my jobs. Here is the stored procedure being used:

CREATE TABLE Patient.TempAppointment --Create Temporary table for 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 into temporary table
FROM 'E:\Data\PatientAppointment.txt'
WITH (
FIELDTERMINATOR='|',
ROWTERMINATOR='\n',
MAXERRORS = 99
)


INSERT INTO Patient.Appointment --Insert data into main table from temporary table where the data doesn't already exist
SELECT *
FROM Patient.TempAppointment
WHERE NOT EXISTS
(SELECT *
FROM Patient.Appointment
WHERE (Patient.TempAppointment.AppointmentID=Patient.Appointment.AppointmentID AND Patient.TempAppointment.ClinicID=Patient.Appointment.ClinicID))
AND Patient.TempAppointment.AppointmentID IS NOT NULL
AND Patient.TempAppointment.PatientID IS NOT NULL


DROP TABLE Patient.TempAppointment --Drop Temporary Table

That is NOT a warning. It is a critical error to enforce referential integrity. I would suggest that when doing imports like this that you first import the data to a staging table. That way you can manipulate and massage that data anyway you need to. — Sean Lange, Nov 17 '16 at 16:08
Would you like to avoid the error or it really doesn't matter the referential integrity? — McNets, Nov 17 '16 at 16:09
you could temporary disable foreigen keys constaints, http://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — McNets, Nov 17 '16 at 16:11
If you want to maintain ref integrity, the only way I can think of to do this is to loop through each row and do a separate insert statement for each row within a try/catch. If it fails you can log it and it will move on to the next row. — Rick S, Nov 17 '16 at 16:12
Delete bad rows using `OUTPUT` clause into special error table to fix the individual issues afterward. `DELETE Patient.TempAppointment OUTPUT DELETED.* INTO TempAppoinmentError WHERE ` — Serg, Nov 17 '16 at 16:27
Thanks for the quick responses, I don't want to ignore the matter and I want to keep referential integrity. I would prefer to skip the row that couldn't find the PK it was looking for and fix it after the fact while continuing the job and procedures stored in it — OM Asphyxiate, Nov 17 '16 at 17:04

score 0 · Answer 1 · answered Nov 17 '16 at 18:35

You can temporary disable checking foreign keys and after import easily cleanse data.

exec sp_msforeachtable "alter table ? nocheck constraint all"

If it's live server copy database structure, import data into temporary database after succesfull cleansing fill this data into live.

Ignore warning in Stored Procedure / Prevent procedure from stopping

1 Answers1