• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Adding multiple rows to an mdb database using OleDb

I have a simple mdb database with a simple table and i have a couple of thousand rows to insert into this table.

If i add them one by one its slow - is there any way to batch them together? In sql you just seperate them with carriage returns but that doesnt seem to work. Can someone give me an example?

0
tfsln
Asked:
tfsln
  • 5
  • 3
  • 2
1 Solution
 
newyuppieCommented:
Just to clarify... Where is the data that you want to insert to the database? A plain text file? Excel? Another MDB?

NY
0
 
tfslnAuthor Commented:
It comes from a web service. But that shouldnt matter should it? The question is quite specific... I already have the data and i have built a number of insert statements like this;

Insert into testtable(test1,test2) Values('test1','test2')

What i want to know is the fastest way of committing those inserts to the database. Im guessing that running them in a batch would be quicker than running them one by one
0
 
peter57rCommented:
Based on what you have said I would only expect there to be one insert query.  Why do you need more than one?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tfslnAuthor Commented:
I gave an example of an insert query i am peforming. I have thousands of those inserts that i specified to perform all at once.
0
 
peter57rCommented:
I have the same Q as NY then.

"Just to clarify... Where is the data that you want to insert to the database? A plain text file? Excel? Another MDB?"
0
 
newyuppieCommented:
0
 
tfslnAuthor Commented:
I dont understand why it is so important to know where my data is coming from. It is not relevant. My question is specfic;

Given a list of transact-sql INSERT statements (the data i used to generate those statemets is not relevant to this question), how can i send that list to access for inserting - as opposed to running the insert one at a time?

In SQL server all you need to do is have a carriage return seperating each INSERT statement but that doesnt work for access and i suspect its a syntax thing.
0
 
tfslnAuthor Commented:
newyupie: I cant make any sense of that link... It appears to me that all he is just doing it the long way by adding parameters using OleDbCommand when he could have just specified them in the Values clause and executed it straight away
0
 
peter57rCommented:
"how can i send that list to access for inserting - as opposed to running the insert one at a time?"

You can't.
Access/Jet SQL only processes one command at a time. You cannot combine commands into one sql script.
0
 
tfslnAuthor Commented:
Ok cool, thats all i needed to know - cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now