locked
URGENT help needed - how to bulk insert from data table into oracle RRS feed

  • Question

  • User1248258412 posted

    I need help to perform mass insert into Oracle database.
    I have a test file like below and separate by ":"
    Every row should have 3 column, I tested insert row by row is working fine but now my problem is I will check the whole batch, example if row 4 only have 2 column, then it will fail.
    And if any text file failed, all the data inside the text file should not be inserted.

    Beside, will it possible to perform mass upload and count total how many records
    being inserted? I tested to read from csv and data table is all working, tested to insert 1 by 1 row also working.

    10001:A01:AAA01
    10002:A02:AAA01
    10003:A03:AAA01
    10004:AAA01
    10005:A05:AAA01

    Saturday, June 18, 2016 2:14 PM

All replies

  • User-2057865890 posted

    Hi Aoshi_kh,

    In following articles, you will learn how to use SqlBulkCopy to insert bulk data to database with Transaction i.e. Commit and Rollback feature in ASP.Net using C# and VB.Net.

    SqlBulkCopy example with Transaction Commit and Rollback in ASP.Net using C# and VB.Net

    Transaction and Bulk Copy Operations

    Best Regards,

    Chris

    Monday, June 20, 2016 7:57 AM
  • User1248258412 posted

    Hi Chris, the both link I saw it and tested before. but my problem is I need to read from text file and insert it to the database.

    and my text file will not contain of header, only I know each row will have 3 column, every row failed will result all the row in same text file to be rollback.

    I did 2 week of research and I still unable to did this, mostly link on Google I read already. and yet still not able to resolve.

    Monday, June 20, 2016 8:13 AM
  • User1248258412 posted

    Anyone know how to solve this? mass upload to check for the column in text, and if any row failed, all the row in the text file should not be inserted.

    And if all working good and able to mass upload, it's able to count total how many records is inserted ?

    Tuesday, June 21, 2016 1:47 AM
  • User-821857111 posted

    If this was my task, I'd probably copy the text file contents into a DataTable and use the OracleBulkCopy class to insert it just like SqlBulkCopy: https://docs.oracle.com/cd/E11882_01/win.112/e23174/OracleBulkCopyClass.htm#ODPNT7518. I would also use C# to validate the file contents before trying to insert them. If they fail validation, I wouldn't even bother trying to insert the data into a database. That way I don't have to worry about transactions. 

    Tuesday, June 21, 2016 11:44 AM
  • User1248258412 posted

    I have added to Datatable as well. but it was for checking the source_no. I have the text file as below, but without header, so I load it into datatable and take the first line source_no to verify the whole file, if any row not same source_no i stop to insert.

    My text file will like this, but without header; each row should have 3 column, if less than or more than 3 column will have to fail whole file not to be inserted.

    ID:CLASS_D:SOURCE_NO

    10001:A01:AAA01

    10002:A02:AAA01

    10003:A03:AAA01

    10004:AAA01

    10005:A05:AAA01

    I tested many way and search around almost 2weeks but still unable to solve, can you share with me how to bulk copy the data to Oracle? and able to let me verify the source_no it's same in the same text file? and also the source_no is not exist in database before?

    Thanks in advances

    Tuesday, June 21, 2016 1:03 PM
  • User-821857111 posted

    The documentation for bulk inserting from a datatable is pretty easy to follow. If you need to validate the data against the database, bulk insert it into a temporary table then compare it to the existing data. You can do that easily enough with JOINs.

    Tuesday, June 21, 2016 2:46 PM
  • User1248258412 posted

    do you have sample that meet my need? bulk insert, check existing data in database, and also check within the same text file?

    Tuesday, June 21, 2016 4:32 PM