Insert multiple rows into database

[Gael Anquetil]

Hello,

I am using  NI_Database_API.lvlib on Labview 2017. I am tringot insrt multiple rows into table but I have the error : 'Semicolon seperatot is missing at the end of the query.'

Plaese find below my command to insert two rows onto one table T_Column ( C_Name as String, C_Status as String).

INSERT INTO T_Column (C_Name , C_Status)
VALUES('21' , 'Not realised'),
('22' , 'Not realised');

How do I must modify the query? Is it possible to insert multiple rows using Microsoft.oledb.4.0?

Thanks for your help.

[smithd]

Depends on the database. For example, oracle does not support multiple inserts in a single query like that. Instead you'd have to use:

INSERT INTO T_Column (C_Name , C_Status) VALUES('21' , 'Not realised');
INSERT INTO T_Column (C_Name , C_Status) VALUES('22' , 'Not realised');

On the one hand this sucks, but on the other hand if you're using oracle this is probably the least of your worries.

 

On a related note, it looks like you are manually generating sql queries. While fine for development, its generally recommended to use parameterized statements to help avoid sql injection attacks.

[Phillip Brooks]

On 6/25/2019 at 11:04 PM, smithd said:

On a related note, it looks like you are manually generating sql queries. While fine for development, its generally recommended to use parameterized statements to help avoid sql injection attacks.

Obligatory Bobby Tables reference

 

[Gael Anquetil]

Does anybody found a solution to execute multiple insert query??

Because now I need to insert like 250000 rows and it takes a journey to do this... I think there is a parameter I need to modify in the connector or seems like that to allow multiple queries.

Thank you for your help...it's quite urgent...

[jcarmody]

I've done it using a parameterized query:

[JKSH]

9 hours ago, jcarmody said:

I've done it using a parameterized query

Wrapping the queries in a transaction ("BEGIN TRANSACTION" + "END TRANSACTION") could speed things up a lot for 250000 rows

[smithd]

https://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle