"Fiddelm3742" wrote:

> 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
>                 }
>         }
>
>
> --
> www.Fiddelke.org

"Cowboy (Gregory A. Beamer) - MVP" wrote:

> 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!
> ***************************
>
>
> "Fiddelm3742" wrote:
>
> > 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
> >                 }
> >         }
> >
> >
> > --
> > www.Fiddelke.org

"Fiddelm3742" wrote:

> 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?
> --
> Matt
> www.Fiddelke.org
>
>
> "Cowboy (Gregory A. Beamer) - MVP" wrote:
>
> > 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!
> > ***************************
> >
> >
> > "Fiddelm3742" wrote:
> >
> > > 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
> > >                 }
> > >         }
> > >
> > >
> > > --
> > > www.Fiddelke.org