LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How do I insert several rows to an Access DB with one SQL statement?

I need to speed up writing to the Access mdb file. Using LV7 and Database Connectivity Toolset. Using the DB Tools Insert Data vi takes 10 seconds to write 2000 records of 9 parameters. Using Paramterized Query and setting the parameters in a loop takes the same amount of time. There is no savings due to setting the Insert command only once.

What is the SQL syntax for sending multiple rows of data in one SQL statement?
Thanks,
Ron Larson
0 Kudos
Message 1 of 4
(5,801 Views)
Hi Ron,

This web site describes how to insert a single row, and also how to insert multiple with an SQL query. It should be a good starting point for the SQL syntax necessary to setup your query.

Scott Y
NI
0 Kudos
Message 2 of 4
(5,784 Views)
Scott,

Thanks for the input but the multiple row statement assumes you already have a second database table with the multiple rows in it. I have an array of data in LV that I want to store as multple rows in the Access mdb file with one SQL statement. Is that possible?

Thanks
Ron
0 Kudos
Message 3 of 4
(5,769 Views)
Hi Ron,

I was able to find many examples for MS SQL Server 2000, and MySQL for how to insert multiple rows with a single query. As you can see here even these two different database servers have different methods for inserting multiple rows.

In every example I was able to find, there was always a note that this method is not compatible with MS Access, or MS Access was not on the list of supported platforms. Even though SQL is more or less a standard, the same features are not implemented on all databases which can communicate through SQL.

From the information I have been able to find, it appears that MS Access does not include support for inserting multiple rows with a single query. As this is an issue with 3rd party software, I am not able to say with complete certainty that there is no support for this feature. What I can say is that the way this feature is implemented on other platforms does not work with MS Access. If you need more information regarding the details of which SQL queries are implemented in Access you will need to contact Microsoft.

At this point there is not much more I can think to offer. If you are able to determine that there is a query that will work with Access, please feel free to post again in this forum with questions regarding how to implement it in LabVIEW.

Scott Y
0 Kudos
Message 4 of 4
(5,728 Views)