How to insert a lot of data into database

Question

I have a programm in C# which downloads data from 4 sources (2 excel sheets,oracle and access database) and calculate them against each other. In the result I have some big results, I keep the results in List. Amount of rows in the result equals approximately 120.000. One row is about 10MB. The result inserts into access database.

How do I insert this list into my databse? Can someone give me an example?

Now I insert rows into table one by one. It costs me about 3 hours to do that.

Holy cow! 120.000 rows of 10 MB each?! What are you doing in that DB? — Randy, Aug 05 '16 at 13:13
Dragging 1.2 GB over the network and inserting to a database taking 3 hours. That' fair. — Zein Makki, Aug 05 '16 at 13:14
Access database. Row contains of 60 columns, it is financial report. — Bushuev, Aug 05 '16 at 13:15
How does 60 financial columns take 10 Mb space??? And since it is beyond limits of 32 bits access, is that 64 bits? I would surely first think of moving on to a real database. — Cetin Basoz, Aug 05 '16 at 13:17
@h.o.m.a.n 60 columns of numbers only ? That should not be 100K and not 10 MB ! — Zein Makki, Aug 05 '16 at 13:18
It is interesting to see that 10 Mb * 120,000 rows fit on a list without giving out of memory exception. Should be a monster super computer. — Cetin Basoz, Aug 05 '16 at 13:19
@TimSchmelter, are you sure 10 Mb * 120000 rows list would fit into your computer's memory when it mean 1.2 Tb and not 1.2 Gb. — Cetin Basoz, Aug 05 '16 at 13:35
It is not a duplicate IMHO. I am not even sure the OP is right on what he is saying. 1.2 Tb and access doesn't really sound realistic together. — Cetin Basoz, Aug 05 '16 at 13:42
I am sorry I did a mistake, when I said, that the row is about 10 MByte. The result database equals 1638906 kbyte. The amount of the rows equals 120000. The row is about 13 Kbytes. — Bushuev, Aug 05 '16 at 13:47

score 2 · Answer 1 · answered Aug 05 '16 at 13:15

Use some kind of bulk insert. If your use entity framework to work with your db's something like this is what you need. Max out the timeout as well.

 using (var transactionScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
            {
                try
                {
                    ctx.BulkInsert(productsToSync, new BulkInsertOptions()
                    {
                        TimeOut = 10 * 60 * 1000
                    });
                    await ctx.SaveChangesAsync();
                }
                catch (Exception ex)
                {
                    SystemLogManager.AddDataSyncErrorLog(ex);
                }
                finally
                {
                    transactionScope.Complete();
                }
            }

Does this require https://efbulkinsert.codeplex.com/? You may want to include that in your answer. — Botonomous, Aug 05 '16 at 13:17

How to insert a lot of data into database

1 Answers1