CODEWITHSUNDEEP
sqlvb.netvisual-studioms-accessbulkinsert
Pezzzz
Pezzzz

Reputation: 748

Inserting large amounts of data in to a database, avoiding RBAR

So I am writing software that has to insert thousands of records into a Microsoft Access. Currently I am using an RBAR(row by agonising row) approach with JET. I wrote a question on SO before asking how to speed up the process and was told to use DAO instead of JET as it would be much faster, and to avoid RBAR.

I am now in the situation where I am ready to implement a faster solution however, I have some further questions. Firstly something simple, does anyone know of a good tutorial for DAO syntax in vb.net as I have tried to look for one online but have struggled. Also does anyone know of a library to use instead?

Secondly what is the best way to avoid an RBAR approach to inserting data into a database. I assume that this is using a record set, but I am unsure how to go about implementing one of these, or the SQL query required to insert several rows at once.

Finally I was told before that ms-access was not the best thing to use a it is slow. Does anyone have any suggestions of a different alternative to access? I would need it to be a free commercial license which I know is a stretch. I only use the database as store for the data I don't need any of the user interfacing that access provides. I found a question on SO about this which suggests SQLite, MySQL or FireBird. Does anyone know of the speed of these solutions compared to Access and whether they are still free for commercial use.

So I am basically looking for advice for how to read/write large amounts of data to a database as quickly as possible. Any help or suggestions would be greatly appreciated.

Upvotes: 1

Views: 1490

Answers (2)

Diego
Diego

Reputation: 36136

Instead of access, take a look at firebird Its free and very reliable.

Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names, since 1981.

The Firebird Project is a commercially independent project of C and C++ programmers, technical advisors and supporters developing and enhancing a multi-platform relational database management system based on the source code released by Inprise Corp (now known as Borland Software Corp) on 25 July, 2000.

Upvotes: 1

Paddy
Paddy

Reputation: 33857

Bulk inserts using MS SQL Express (free edition):

http://www.sswug.org/articles/viewarticle.aspx?id=35680

Basically involves using the bulk insert syntax and passing in a file:

BULK INSERT Test..Clients FROM
'c:\TestData.csv'
WITH (
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

Upvotes: 1

Related Questions