how to insert data into database even after few records are wrong

hi to all,

here i am using local transaction where i am specifying the start,commit,rollback concept now let use assume that there r 10 records there is an error in 6 record but in database it should insert 9 records except error once so please tell me the sequence steps what to use and how to place them only error records should not be saved in database and database is sql server 2008 its urgent please

Rather than just provide a direct answer I’d ask you to think about what is needed in this case. You’re looking for each record to be committed independently of all the other records. How would you do that given the start, commit, rollback services you mentioned?

I’m with reamon. Also, here are some other options that depend on your ultimate volume of inserts, expected failures, and SQL expertise. Depending on what’s failing, you may want to consider editing your data more carefully before inserting it into the database, rather than relying on individual records erroring out on insertion as a primary method of error detection. If you have to do some relational database checks on insert, you might do those beforehand as well, or call a stored procedure to make those checks and do an insert with a soft failure that won’t roll your transaction back (it would have to be written to specifically work this way). Another option, again if volume merits and you have to do a lot of checks against existing database records, you could dump your data to a less restrictive table and call a stored procedure to do the final editing and report errors instead of failing.