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

[Pierre_F]

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

[K.Ross]

Hi Pierre

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

Thanks

[wiebe@CARYA]

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

[Yamaeda]

---

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

[wiebe@CARYA]

---

@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.

[Pierre_F]

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

[wiebe@CARYA]

---

@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

---

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.