how transmit array elements to a table of database at once
-
Hi I have 4 arrays which i want to insert them into 4 columns of a table of my sql server 2000 database, and it should be done at once, not inserting one by one. How can i do this?
Transactions :) http://en.wikipedia.org/wiki/Atomicity\_(database\_systems)
-
Hi I have 4 arrays which i want to insert them into 4 columns of a table of my sql server 2000 database, and it should be done at once, not inserting one by one. How can i do this?
-
Easiest way is to create a stored procedure and pass the data as parameter to server side. This requires only single call to db.
The need to optimize rises from a bad design. My articles[^]
I have read somewhere that I should use union all like this:
use myDb insert into myTable (columnName1,columnName2...) select row1value1,row1value2... union all select row2value1,row2value2... union all . . .but I don't know how pass the arrays to the stored procedure and how perform these in a loop to add all array elements automatically...:confused: -
I have read somewhere that I should use union all like this:
use myDb insert into myTable (columnName1,columnName2...) select row1value1,row1value2... union all select row2value1,row2value2... union all . . .but I don't know how pass the arrays to the stored procedure and how perform these in a loop to add all array elements automatically...:confused:Union is only usable in db when you combine several result sets. You cannot use it to pass arrays from client. I don't remember if you can pass table type parameter in sql server 2000. That would be a solution in your case. It's not automatic so you must program the insert loop inside the procedure. Another option is to use SqlBulkCopy class. Have a look at that also.
The need to optimize rises from a bad design. My articles[^]