LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

DTC : how to insert big amount of data in one shot to a SQLserver table

Hello

One of my computers is connected to a distant SQLServer database. sometimes, this PC have to insert around 4000 new lines in one shot.

for this, the function "DB Tools Execute query" is used 4000 times

 

Problem: it takes a lot of time
 

Is there another possibility to send large amount of data, like what it's possible in visual studio?

 

thanks in advance

Pierre FCentum TNS, Grenoble
Certified LabVIEW Associated Developer
0 Kudos
Message 1 of 7
(3,342 Views)

Hi Pierre

 

Did you find a solution to this? I would be interested to hear how you resolved this. 

 

Thanks

0 Kudos
Message 2 of 7
(3,089 Views)

It depends on the database. AFAIK Visual studio is not a database.

 

Some DB's accept inserts like this:

INSERT INTO table1 (First, Last) VALUES ('Fred', 'Smith'), ('John', 'Smith'), ('Michael', 'Smith'), ('Robert', 'Smith');

 

It would probably still be faster if ASCII data could be avoided, but that raises even more questions about the used DB, used toolkit, etc.

 

https://teamtreehouse.com/library/adding-multiple-rows-to-a-table

0 Kudos
Message 3 of 7
(3,078 Views)

wiebe@CARYA wrote:

 

Some DB's accept inserts like this:

INSERT INTO table1 (First, Last) VALUES ('Fred', 'Smith'), ('John', 'Smith'), ('Michael', 'Smith'), ('Robert', 'Smith');

 


I would say ALL DBs should support that. I'm pretty sure it's part of the SQL standard. 🙂

However listing all 4000 will probably not work, there's often a limit to 4k or 8k characters in the command, so you'll have to do it in groups of 50-100.

/Y

G# - Award winning reference based OOP for LV, for free! - Qestit VIPM GitHub

Qestit Systems
Certified-LabVIEW-Developer
Message 4 of 7
(3,072 Views)

@Yamaeda wrote:

wiebe@CARYA wrote:

 

Some DB's accept inserts like this:

INSERT INTO table1 (First, Last) VALUES ('Fred', 'Smith'), ('John', 'Smith'), ('Michael', 'Smith'), ('Robert', 'Smith');

 


I would say ALL DBs should support that. I'm pretty sure it's part of the SQL standard. 🙂

However listing all 4000 will probably not work, there's ofter a limit to 4k or 8k chars in the command, so you'll have to do it in groups of 50-100.

/Y


From what I read SQL Server supports it starting from version 8. Not sure what's the current version, might be version 4563 for all I know. So at the moment it could be common for all DB's, it might even be added to the standard at some point. But it has not always been available.

0 Kudos
Message 5 of 7
(3,064 Views)

Hi K.ross

 

I solved this problem by using .NET framework object: "SQLBulkCopy" instead of the Database Connectivity Toolkit supplied by NI

 

https://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

 

 below you can fin a screenshot of a part of my code. You have to

1)assemble SqlBulkCopy with an SqlConnection (connection chain similar to Database connectivity toolkit)

2)create your table with "DataTable" and personalize your columns

3)Add the values

4)close all references

Sans titre.png

Pierre FCentum TNS, Grenoble
Certified LabVIEW Associated Developer
0 Kudos
Message 6 of 7
(3,038 Views)

@Pierre_F wrote:

Hi K.ross

 

I solved this problem by using .NET framework object: "SQLBulkCopy" instead of the Database Connectivity Toolkit supplied by NI

 

https://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

 

 

Sans titre.png


That will give you tons of memory leaks if you don't close those references!

 

Also, for loops can iterate 0 times, so references (and errors) need to use a shift register! Or the error\reference will be default, and potentially errors will be cleared, and refs won't be closed.

Message 7 of 7
(3,034 Views)