Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajuneo
Starting Member
3 Posts |
 Posted - 2008-08-20 : 01:09:51
|
| MS SQL Server supports MR-Insert through ODBC access mechanism. Setting SQL_ATTR_PARAMSET_SIZE attribute to more than 1 enables statement handle to perform multiple row insert operation. Array host variables will be parameterized to statement handle using SQLBindParameter function. Statement execution may result errors. Following attributes need to be defined for a statement to evaluate errors further:SQL_ATTR_PARAMS_PROCESSED_PTR : No. of processed rows (including error sets)SQL_ATTR_PARAM_STATUS_PTR : Status for each set of values processed (i.e., row)Possible values for this attribute are SQL_PARAM_SUCCESS, SQL_PARAM_SUCCESS_WITH_INFO, SQL_PARAM_ERROR, SQL_PARAM_UNUSED, and SQL_PARAM_DIAG_UNAVAILABLEBy default, it supports non atomic multi row insert. i.e., processing rows will be continued though errors are encountered. My question is Does MS SQL Server support atomic multi row insert?Here, atomic specifies that if the insert for any row fails, all changes made to the database by any of the inserts, including changes made by successful inserts, are undone. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-20 : 01:23:00
|
| i think you need to enclose the full set in a transaction and will have to explicitly rollback entire set in case of any intermediate error. |
 |
|
|
rajuneo
Starting Member
3 Posts |
Posted - 2008-08-20 : 01:43:25
|
| You may be correct. But this is not direct solution. If I use the same transaction for other operations, they will be rollbacked. I don't want this. Don't we have any handle statement attributes which control the multi row insert operation? |
|
|
|
|
|
|
|
|
Multi row insert using ODBC in atomic fashion