Problem related to insert a group of record to a data file ?

BarryTang
BarryTang used Ask the Experts™
on
I have a problem as follows :

I have a insert statement to insert a group of records
into a data file similiar as follows

 insert into file_a select * from file_b

However, some of the records in file_b is not suitable
for insertion to file_a, so that all records are
rejected.

My question is :

Can I have a way so that only those unsuitable records are rejected ( and give some notification such
as SQL mail to operator ), and the other suitable records
will be inserted into file_a ?






Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can go through your input table record by record inserting one at a time (using an identity on that table?) and alerting if the input fails. Better to put a status on the input table which is set with the input result.

Better to check the input table before the insert and set the status depending on whether the data is correct. This leaves the error processing for database errors rather than normal processing.
And an sql statement (like insert) is atomic - i.e. it will either complete or fail - it cannot partially complete.
Commented:
you could add a WHERE clause to the input select statement and state there the conditions for what are "suitable" records.

example
1)if the process fails because of a primary key violation

     insert into TableA
     select * frm TableB
     WHERE TableB_Id not in (select TableA_Id from TableA)

    this qurty adds records depending on the fact that
    the ID doesnt already appear in the target table

2)if the process fails because of a conversion from char to integer error

     insert into TableA
     select * frm TableB
     WHERE isnumeric(TableB_Charcol) = 1

     this qurty adds records depending on the fact that
     the a character column in TableB holds numeric data
BarryTang,
You have 17 opened questions as of this date.  
I will be posting in each of these questions.  
Please take care of your opened questions older than 30 days by clicking on your logon name link and looking for UnlockedQ or using the links I am providing below.  
Please finalize these within 72 hours or I will ask administration to look into your account at that point.  If you have questions, please ask and I will provide assistance.  

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20187331.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20133725.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20088012.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20083509.html
http://www.experts-exchange.com/Miscellaneous/Games/Q_20382035.html
http://www.experts-exchange.com/Programming/Game_Development/Q_20382294.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20534626.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20521771.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20515842.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20513575.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20493221.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20436510.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20399960.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20395868.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20375410.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20373966.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20372811.html

Thank you.
Administrative Action - Force Accepted.

SpideyMod
Community Support Moderator @Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial