Bulk Insert into access database from c#?

Question

How can I do this. I have about 10000 records in an an Excel file and I want to insert all records as fast as possible into an access database?

Any suggestions?

Where are the records? In another database, in a data file? What format? — tijmenvdk
– tijmenvdk, Commented Jan 10, 2011 at 12:28
Do you mean fast = performant, or fast = easy? Also, +1 on "Where are the records?" — Iain Galloway
– Iain Galloway, Commented Jan 10, 2011 at 13:02

HABJAN · Accepted Answer · 2011-01-10 12:57:45Z

1

What you can do is something like this:

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Test Files\db1 XP.mdb") 
    AccessConn.Open() 
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[ReportFile.txt]", AccessConn) 
    AccessCommand.ExecuteNonQuery() 
    AccessConn.Close()

Switch off the indexing on the affected tables before starting the load and then rebuilding the indexes from scratch after the bulk load has finished. Rebuilding the indexes from scratch is faster than trying to keep them up to date while loading large amount of data into a table.

If you choose to insert row by row, then maybe want to you consider using transactions. Like, open transaction, insert 1000 records, commit transaction. This should work fine.

answered Jan 10, 2011 at 12:57

HABJAN

9,2733 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilyas Over a year ago

Hi What is this code on the 3rd line - the ReportFile and the [Text;Database=XXX]

Fionnuala Over a year ago

@Ilyas Have a look at connection strings, the above is for text, you can also use Excel strings: connectionstrings.com/excel

David-W-Fenton Over a year ago

Why drop indexes for just 10K records? That's peanuts. And when you put them back on, it's going to have to populate them, anyway, so you really haven't gained all that much. And, frankly, I'd rather have data rejected if it violates the indexes, than have re-creating the indexes fail because the data violates one or more of them.

tijmenvdk · Accepted Answer · 2011-01-10 12:29:44Z

0

Use the default data import features in Access. If that does not suit your needs and you want to use C#, use standard ADO.NET and simply write record-for-record. 10K records should not take too long.

answered Jan 10, 2011 at 12:29

tijmenvdk

1,75810 silver badges19 bronze badges

3 Comments

Fionnuala Over a year ago

There is no difficulty in writing an sql statement to run against a connection that will insert all records at once, unless they are in some pretty obscure format.

Ilyas Over a year ago

Record for record takes too long and isnt acceptable for me. The records are in an excel file.

tijmenvdk Over a year ago

If you know up front your source is an Excel file, it helps if you put that in the question. This stops people from guessing your scenario. Since you tagged your question with C#, I am assuming you want to do this from code, right?

Collectives™ on Stack Overflow

Bulk Insert into access database from c#?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related