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.
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.
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.
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 ?
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.
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?
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.
Member
92 Points
406 Posts
URGENT help needed - how to bulk insert from data table into oracle
Jun 18, 2016 02:14 PM|aoshi_kh|LINK
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
All-Star
17592 Points
3510 Posts
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 20, 2016 07:57 AM|Chris Zhao|LINK
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
Member
92 Points
406 Posts
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 20, 2016 08:13 AM|aoshi_kh|LINK
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.
Member
92 Points
406 Posts
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 21, 2016 01:47 AM|aoshi_kh|LINK
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 ?
All-Star
187599 Points
27186 Posts
Moderator
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 21, 2016 11:44 AM|Mikesdotnetting|LINK
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.
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
92 Points
406 Posts
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 21, 2016 01:03 PM|aoshi_kh|LINK
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
All-Star
187599 Points
27186 Posts
Moderator
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 21, 2016 02:46 PM|Mikesdotnetting|LINK
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.
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
92 Points
406 Posts
Re: URGENT help needed - how to bulk insert from data table into oracle
Jun 21, 2016 04:32 PM|aoshi_kh|LINK
do you have sample that meet my need? bulk insert, check existing data in database, and also check within the same text file?