Insert quits when exception is thrown

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to update a large set of data (VS2005 & SQL2000). I have a
temp_table setup to hold my data taht i read in from my datafile. I BCP all
of the data to that table and I'm then running insert/update statement(s) on
it to move the corresponding data to my production server. However, I can
not garauntee that all of the data I am receiving is up to date so sometimes
there are exceptions thrown when a FK doesn't exist (as it should) however
I'd like for my data processing to continue and just discard the row that is
causing the problem. As far as I know there is no way for me to validate
that all of the data I have is correct, just rely upon my RI to keep it as
correct as possible. Is it possible for my Program to continue after an
exception is thrown and caught? I have attached a portion of the exceptions,
and some of the code as well.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_debt_client'. The conflict occurred in database 'Depted-TEST', table
'client', column 'clientid'.\r\nUPDATE statement conflicted with COLUMN
FOREIGN KEY constraint 'FK_debt_client'. The conflict occurred in database
'Depted-TEST', table 'client', column 'clientid'


static void BcpUpdate(SqlConnection DWConnection, DataTable datatable,
String UpdateString, String InsertString)
{

SqlTransaction tx = DWConnection.BeginTransaction();

try {

using(SqlBulkCopy bcp = new SqlBulkCopy(DWConnection,
SqlBulkCopyOptions.Default, tx))
{
bcp.DestinationTableName = "temp_" + datatable;
bcp.BatchSize = 5000;
bcp.WriteToServer(datatable);
}
tx.Commit();

tx = DWConnection.BeginTransaction();
SqlCommand cmd = new SqlCommand(null, DWConnection, tx);
cmd.CommandText = @"" + InsertString + UpdateString;
cmd.CommandTimeout = 0;

cmd.ExecuteNonQuery();
tx.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
//tx.Rollback(); taken out for troubleshooting
}
}
 
1. Load the data
2. Dump any data that is invalid
3. Move valid rows to proper table.

Don't play the "throw it up against the wall and see what sticks" game, as
it waste far too much IO and forces you to catch consistently.

To make this even less expensive, you can set up the whole job in a stored
procedure and fire it off once. Following what you are doing (not completely
agreeing with some of the naming method, but rolling with the idea.

The basic statements to focus on are

DELETE FROM tempTable
WHERE BadForeignKeyField NOT IN (SELECT ForeignKeyID
FROM ForeignKeyTable)

Then, it is an easy job of:

INSERT INTO SomeTable (field1, field2, field3)
SELECT field1, field2, field3
FROM tempTable

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks for the reply, but I have a couple of problems with your solution.
Mostly the Dump any data that is invalid. I'm not exactly sure how to check
what data is invalid. Yes it's WHERE BadForeignKeyField NOT IN (SELECT
ForeignKeyID FROM ForeignKeyTable) but I'm not sure how to get that data in
this instance because of the type of application I"m writing. It's a generic
app to load any table I want. I dont know what tables the FK's are in is the
problem. The way I'm generating the insert and update statements is by
finding what the PK's of the table are using INFORMATION_SCHEMA.COLUMNS, and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE @ runtime to generate my Insert and
update statements before I run them. Now, I can find what the FK's of the
table are, but I dont know how to find the tables that they reside in, also
it doesn't really make sense for me to create relationships in a dataset and
load the data from the talbes into there simply because I'm dealing with such
a large amount of data. My program on a daily basis will update anywhere
form 100,000 to 4+ million rows of data depending on what tables it runs on.
So creating relationships in a dataset and populating them doesn't seem like
a valid idea either. You dont by chance know a way that I can dynamically
write that delete from SQL statement and then execute that do you?
 
Hate to resopnd to my own post, but wont this be a pretty heavy query to run
as well to Delete rows that don't have FK's that exist being my tables have
FK's to multiple tables and I have a lot of data to go through.
 
Back
Top